topic Re: unusual distinct value counting in QlikView

unusual distinct value counting

Mon, 04 Mar 2013 09:09:51 GMT

Hi guys, I hope on yours help in one interesting calculation using Set analysis.

1. calculation(Nr of payers) should represent count of distinct payers in range of current period and all above periods, for example, period 2010.10 should show unique count of payers which is in 2010.10, 2010.09, 2010.08, 2010.07(absence), 2010.06.

2.calculation (Last 3 months) the same but only for last 3 month, for example, period 2010.09 should show unique count of payers which is in 2010.09, 2010.08, 2010.07(absence).

Thank your for any help.

Re: unusual distinct value counting

Gysbert_Wassenaar — Mon, 04 Mar 2013 09:32:28 GMT

The sets in set analysis expressions are calculated per chart, not per row. So if you want to use the period as a dimension set analysis won't help you. If you're willing to select a period then you can use these expressions:

Count({<[Payment Date]={'<=$(=max([Payment Date]))'}>}distinct %CaseIDKey)

Count({<[Payment Date]={'>$(=addmonths(max([Payment Date]),-3))'}>}distinct %CaseIDKey)

Otherwise you'll have to calculate a running count in the script and add an AsOf table for the 3-month period. See attached qvw

Re: unusual distinct value counting

Tue, 05 Mar 2013 12:43:58 GMT

Thanks for your answer, could you please explain me these expressions

Count({<[Payment Date]={'<=$(=max([Payment Date]))'}>}distinct %CaseIDKey)

Count({<[Payment Date]={'>$(=addmonths(max([Payment Date]),-3))'}>}distinct %CaseIDKey)

, because when I have substituted in chart , it showed different result comparing of previous result

Re: unusual distinct value counting

Tue, 05 Mar 2013 13:26:08 GMT

Could you please insert it in example .qvw because i have different results. Thanks

Re: unusual distinct value counting

Thu, 07 Mar 2013 14:10:39 GMT

But what I should do if my report has to have %CaseIDKey filter?