One Conditional Formula Depends on Another

[bobhagan]

I have a table that records contacts with clients. The filter is contacts for the last month. There are groupings by client, worker, supervisor, site and a total.

For each client I have one formula that records successful contacts and and one that records unsuccessful attempts. (There is also a field that records the reason for non-succcess) I only want to count unsuccessful attempts if there was no successful contact. I've sorted so CMP comes before MISS. The VisitComplete formula is simple:

If stat = "CMP" THEN client_id

I can get a distinctcount(client_id) on all footers.

However, for non-completion, I've tried all sorts of running total-like formulas with variables to capture the completion, and use it to suppress adding anything to the non-completion count. None work. If you add any variable into a formula, the summary choices are removed from the menu. Doing summary formulas by hand gets: "the summary/running total could not be created". I finally ended up with:

If stat = "MISS" Then client_id,

and a client footer formula:

IF DistinctCount({@ReqVisitCmp}, Client.client_id ) = 1
THEN {@Null}
ELSE DistinctCount({@ReqVisitNotCmp}, Client.client_id )

That works for display, but I can't do distinctcounts for any further groupings.

Any help on this would be appreciated.

Bob Hagan

 

[synapsevampire]

Try:

If stat = "MISS" Then
1
else
0

Now it should always show up for evaluation by formulas/aggregates.

Not sure why you're using the distinctcount function if you're trying to count all of the unsuccessful contact attempts, that would only show one per client ID wouldn't it?

-k