Count distinct calculation

[Arsenal]

Hey all,

I need to create a calculation for the following scenario:

Refund_CD       Customer ID        Amt
0000               1111                  20
0000               1111                  30
0001               1111                  -30
0000               3333                  100
0002               3333                  -100

Basically, the refund_cd will be the same number, irrespective of customer id, when there has been no return. If there has been a return, then the refund_cd will be a unique number and I need a count of those unique numbers

in algorithmic terms, it would be a count distinct refund_cd  for (amt*-1) > 0

I have tried a count(distinct(refund_cd)) for (amt*-1)>0 but apparently there's a syntax error there

I have tried an IF Then but that doesn't work either.

Any suggestions?

[rockytopmark]

count(Distinct [Refund_CD] for [Customer Id]) will give you the count of different refund codes for each customer ID.

Not sure that is what you want.

If you want to get the count of Refund_CD for each customer but only those with amt < 0, then use If-Then-Else.  Name this column "Countable_Flag"

If (amt < 0)
Then (1)
Else (0)

Then just do a Total of the this value.
Total([Countable_Flag] for [Customer ID])

HTH... Mark

[Arsenal]

Thanks for your help, rocky.

Previously, I had tried to use something similar. The concerned query subject did contain a calculation which determined if the amt>0 or no, and I had tried to create a query item which did a count distinct for the calculation query item, but I  kept getting a DB error.

A count distinct on another query subject for just the amt column (not the calculation one), returns the correct result.

So, basically, I need to be able to create a query item that will check a 2nd query item's condition(within the same query subject) and if true will count a 3rd query item, which is the code, within the same query subject

I'm at my wit's end..i mean, it shouldn't be this tough No matter what I do, the moment I do a check for the calculation query item to be true I get database errors :-[