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.
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.
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.
