Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sample Data:
customer_ID DATE qty
97 9/22/2021 25
97 1/24/2022 190
97 1/25/2022 9
97 2/24/2022 20
97 2/25/2022 56
93 6/5/2020 8
93 8/3/2020 23
93 8/4/2020 8
93 8/6/2020 3
93 12/17/2020 15
93 12/21/2020 1
93 2/4/2021 9
93 2/8/2021 4
93 9/9/2021 4
93 1/7/2022 7
93 2/15/2022 44
93 2/18/2022 50
For each Cust id and date , I need sum the value for last 30 days(for those dates purchase is made) like below table i have to create calculative column Trailing 30 days qty.
if no previous dates fall in 30 days than have same value. I am able to do that on expression but i need to create column on load script:
sum({$< DATE = {"$(='>=' & Date(DATE -30) & '<=' & Date(DATE))"}>} qty)
sum({$< DATE = {"$(='>=' & 12/26/2021 & '<=' & 01/25/2022"}>} qty)
Means for data 1/25/2022 the sum up the value from 12/26/2021 so 190+99
if 2/24/2022 then whatever dates fall for last 30 days to sum so 190+9+20 so on for each group of ID i have check on dates and sum for last 30 days. I tried intervalmatch but did not get the desired results.
Thank you in advance. let me know if any question
PRO
In your example, the 1/7/2022 is way beyond the 30days limit but still being accumulated to 2/18/2022!!
Many Thanks. your solution worked. yes thats my calculation mistake.
Create an AsOF table as below
Table1:
Load *,customer_ID&'-'&DATE as Key inline [
customer_ID,DATE,qty
97,9/22/2021,25
97,1/24/2022,190
97,1/25/2022,9
97,2/24/2022,20
97,2/25/2022,56
93,6/5/2020,8
93,8/3/2020,23
93,8/4/2020,8
93,8/6/2020,3
93,12/17/2020,15
93,12/21/2020,1
93,2/4/2021,9
93,2/8/2021,4
93,9/9/2021,4
93,1/7/2022,7
93,2/15/2022,44
93,2/18/2022,50
];
AsOf:
Load *
Where Exists(DATE,AsOfDate);
Load
customer_ID as customer_ID2
,DATE as DATE2
,customer_ID&'-'&Date(DATE - Iterno()+1) as Key
,Date(DATE - Iterno()+1) as AsOfDate
Resident Table1
While DATE-32 <= DATE - Iterno()+1;
exit Script;
In Chart:
Dimension: customer_ID, DATE
Measure:
Qty = Sum(qty)
30 Days Trailing Qty = sum(aggr(Sum(qty),customer_ID2,DATE2))
In your example, the 1/7/2022 is way beyond the 30days limit but still being accumulated to 2/18/2022!!
Many Thanks. your solution worked. yes thats my calculation mistake.
@vinieme12
if I would like to do sum for previous months only do I use IterNO() -1? I used >= start(lastmonth )and <= Enddate(LastMonth)
Your response is very useful but still not understanding Iterno()
Thank you @vinieme12 Its helpfull but still not understanding IterNo()
Like to sum for only previous month qty. just for my learning. How does as-of work on on previous month.