topic Re: Re: Re: Rolling 3 month count distinct in QlikView

Rolling 3 month count distinct

Anonymous — Thu, 26 Jun 2014 12:55:50 GMT

Hi,

I have a problem figuring out how to calculate a rolling total of distinct customers to be used in a chart/table with Period (YYYYMM) as single dimension.

No problem sorting out e.g.:

1. Monthly Sales: sum(Sales)

2. 3M Rolling Monthly sales: sum(aggr(rangesum(above(SUM(Sales),0,3) ),Period))

3. Distinct number of customers per month: count(DISTINCT Name)

4. 3M non-distinct number of customers (which is fairy useless as a KPI): sum( aggr( rangesum( above(COUNT(DISTINCT Name),0,3) ),Period))

See example document for some example data. The idea is to end up with the number 3 for period 201303 rolling 3 month.

I have been able to create a dirty solution with a date island and if-statements, but would hope to have something based on aggr/above.

Kind regards

Niklas

Re: Rolling 3 month count distinct

vgutkovsky — Thu, 26 Jun 2014 14:23:53 GMT

The expression would be something like this, with Full Accumulation enabled on the expression:

sum(aggr(

if(Period = min(total <Name> Period),1)

,Name,Period

))

See attached.

Regards,

Vlad

Re: Re: Rolling 3 month count distinct

Anonymous — Thu, 26 Jun 2014 14:46:23 GMT

I should have been clearer with my request regarding the three month rolling.

Your solution solves a possible "since start" distinct queries, which is of course the same for only 3 months.

With a longer time frame (now attached) it misses my problem.

Maybe I should add that I did a similar if statement with my date island IF(period<=island_period and >island_period-3), but I would hope to avoid if's and date islands.

Kind regards

Niklas

Re: Re: Re: Rolling 3 month count distinct

vgutkovsky — Thu, 26 Jun 2014 15:32:50 GMT

Ah, I thought the 3 months was just an example, not the specific requirement. I think your idea to use date islands is absolutely correct in that case. Alternatively, I'm attaching another way to accomplish what you want, but this requires a slight data model change to ensure that each customer is associated to each Period.

Regards,

Vlad