topic Re: How to visualize unique values from a sliding timeframe? in QlikView

How to visualize unique values from a sliding timeframe?

Tue, 18 Jun 2013 05:03:58 GMT

Hello, I have a problem where I need help from you experts.

I want to count unique customerId within a range of months and show this in a graph.

Let's assume I want to show the unique customerId within a sliding 3 months 'window'.

I have a table looks like below.

Year-Month CustomerId
2012-01 A01
2012-01 B01
2012-02 A01
2012-02 A01
2012-02 C01
2012-02 B01
2012-03 A01
2012-03 B01
2012-03 B01
2012-03 D01
2012-03 E01
2012-04 B01
2012-04 A01
2012-04 B01
2012-04 B01
2012-04 C01
2012-05 D01
2012-05 A01
2012-05 B01
2012-05 C01
2012-05 A01
2012-06 B01
2012-06 B01
2012-06 C01
2012-06 A01

Based upon above chart, the result I want to present is as below.

Year-Month Unique-CustomerId
2012-01 2
2012-02 3
2012-03 5
2013-04 5
2013-05 5
2013-06 4

'window' for 2012-01 is 2012-01
'window' for 2012-02 is 2012-02 and 2012-01
'window' for 2012-03 is 2012-03, 2012-02 and 2012-01
'window' for 2012-04 is 2012-04, 2012-03 and 2012-02

and so on...

any ideas how to make above happen? I have been struggeling with Range-functions, aggr-functions but not managed to get it all together.

Re: How to visualize unique values from a sliding timeframe?

AbhijitBansode — Tue, 18 Jun 2013 06:49:11 GMT

See the attached app (edit script).

Hope it suffice your requirement.

Regards,

Abhijit

Re: How to visualize unique values from a sliding timeframe?

Tue, 18 Jun 2013 07:56:43 GMT

Hi, thank you for the quick response. It seems not to work out the way I want it to. The last month (06) ends up with 5 unique customerId. It should be 4.

Re: How to visualize unique values from a sliding timeframe?

AbhijitBansode — Tue, 18 Jun 2013 08:50:42 GMT

My mistake, I assumed window period for each month is that month plus previous all months of the year.

But, it is current month plus previous two months.

I have made the changes accordingly.

PFA.

Regards,

Abhijit