topic Re: Cumulative count distinct in QlikView

Cumulative count distinct

Fri, 31 Mar 2017 18:25:58 GMT

Hi guys,

I have to calculate a cumulative count distinct from the very beginning of my data. The "distinct" has to consider the whole data set of course.

I am currently using this formula:

if(count(distinct [Number of Active Suppliers])<>0,num(count({<MonthYear={'<=$(=max(MonthYear))'},Year,Month>} total <MonthYear>distinct [Number of Active Suppliers]), '#.##0'))

which is fine for monthly values, but it does not work when I make multiple selections on months (for every month I get the value of the last one selected).

Any idea?

Many thanks,

Matteo

Re: Cumulative count distinct

Gysbert_Wassenaar — Sat, 01 Apr 2017 07:59:49 GMT

I think you'll have to use an AsOf table in this case. With sums you have other options but those don't work for a cumulative count distinct. Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

Re: Cumulative count distinct

Sat, 01 Apr 2017 15:20:45 GMT

Hi Gysbert,

I tried the AsOf table as you suggested, in particular using this process:

The As-Of Table

I can accumulate intra-year values very easily, but how can I use this approach to get accumulated values from 2004 up to now?

Thanks,

Re: Cumulative count distinct

Sat, 01 Apr 2017 16:31:00 GMT

Gysbert,

I just noticed that you commented Henric's blog regarding my specific problem. In fact the biggest problem I am facing at the moment is that the selections on the dashboard must be made on "normal month" and "normal year" fields.

So, as you correctly pointed out in your comment, it seems that AsOfMonth field works on the contrary if the selections are made on "normal" date fields.

Have you developed a work around for this situation?

At the moment, my formula is:

count({<YearDiff={'<=13'},AsOfMonth={'<=$(=min(AsOfMonth))'},Year,Month>} distinct [Number of Active Suppliers])

The issues are:

- make the set analysis select the proper value for AsOfMonth (for multiple selections as well)

- showing values ONLY for the selected year, maintaining the accumulation

Many thanks.

Re: Cumulative count distinct

Gysbert_Wassenaar — Mon, 03 Apr 2017 17:54:53 GMT

Perhaps something like this:

count({<AsOfMonth={'>=$(=Date(MakeDate(2014),'YYYY MMM'))<=$(=Date(Today(),'YYYY MMM'))'},Year,Month>} distinct [Number of Active Suppliers])

You may have to adjust the date format string to whatever your AsOfMonth values have

Re: Cumulative count distinct

Gysbert_Wassenaar — Mon, 03 Apr 2017 17:58:09 GMT

- make the set analysis select the proper value for AsOfMonth (for multiple selections as well)

- showing values ONLY for the selected year, maintaining the accumulation

If you want to use the selections in your regular Year and Month fields as selections for the AsOf fields then do something like:

count({<AsOfYear=P(Year),AsOfMonth=P(Month),Year,Month>} distinct [Number of Active Suppliers])

Re: Cumulative count distinct

Tue, 04 Apr 2017 08:17:59 GMT

Hi Gysbaert,

that's exactly how I solved this issue just some hours before your answer

Thanks!