Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
See the attached app (edit script).
Hope it suffice your requirement.
Regards,
Abhijit
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.
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