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?
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
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 :-[