Conditional distinct count 1

[rq]

I created a running total field that will do a distinct count on the customer id. I want the distinct count to tally only if the customer type is active. How do I define my formula within the Evaluate section? I used the IIF function but I didn't get the desired result. As an fyi, this is the formula I used: IIF ({ado.customertype}='Active', 1, 0).

 

[lyanch]

Edit the running total..in the evaluate for section, select use a formula.. for the formula use:

{ado.customertype}='Active'

The the count will only evaluate for Active customers.

Lisa

 

[rq]

I followed those steps and did not get the result I was looking for.

 

[lyanch]

What results did you get? This should count distinct (whatever field you used) for only active records.. You may need to change the field you are counting or something else. If you give some sample data and expected results it will make it easier.

Lisa

 

[rq]

Lisa

My report data included active and inactive customers. It tallied a distinct count of the customer id alright but it included the inactives.

rq

 

[stormtrooper]

What Lisa suggested should work?? Did you type in exactly as you would see if you just displayed the field itself? That is, no spaces, etc.?

 

[lbass]

Is it possible that this is a history field and the field picks up both "active" and "inactive" statuses for the same customer? In other words, a distinctcount would pick up all customers who had ever been active. If this is the case, then you need to use a formula like the following in the running total:

maximum({ado.customertype},{ado.customerID}) = "Active" //this requires a group on {ado.customerID}

-LB

 

[rq]

My apologies to Lisa. Stormtrooper was right. Initially, my Boolean formula had spaces. Thus, the problem. When I made the adjustment to exclude the spaces in the formula, I got the results I needed.