Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I need to create a bar chart that counts distinct customers by week cumulatvley.
Here is the expression I have used at present (which works ok when i plot each week individually
({<[Year] = {$(vMaxYear)}>}DISTINCT Customer)
If I select full accumulation it sums the totals from each week but doesn't remove duplicates in the customer number.
I think i probably need to use a rangesum but i'm not sure how to write this.
Any suggestions?
Perhaps you can get the needed result with an AsOf table where every week is associated with itself and all its previous weeks. That way you should be able to get the accumulated distinct count. See this document for more information: Calculating rolling n-period totals, averages or other aggregations
Thanks for this, I've tried to add this to my script but it fails to load.
I have over 1,400,000 transactions to calculate against.- woudl this be causing the problem?
I tried the following
AsOfWeek:
load
FWeek as FWeek_AsOf,
FWeek + 1 - IterNo() as FWeek
Resident DEMAND
while IterNo() <= 52;
right join load FWeek Resident DEMAND;
AsOfWeek:
load 'Current' as Type,
FWeek as FWeek_AsOf,
FWeek as FWeek
Resident DEMAND;
Concatenate (AsOfWeek)
load 'Rolling 52' as Type,
FWeek as FWeek_AsOf,
FWeek + 1 - IterNo() as FWeek
Resident DEMAND
while IterNo() <= 52;
right join load FWeek Resident DEMAND;
Two things spring to mind: