Hello!
I am trying to loop over a date table which has a period field (202301,202302,...,202313). I need to calculate Rolling 13 metrics from any given period from a fact table for all historic periods at Store level. I am able to create the workflow for one Rollling 13 Period by hardcoding the filter for PeriodID >= 202210 and PeriodID<=202309. I want to automate this passing of value so that the entire workflow runs once for a period value passed to the filter, appends it to output then repeats.
Step 1 - Filter Single Colum Period Range
DimTable.PeriodID => 202301 and DimTable.PeriodID <=202313
Step 2 - Loops
Sample Runs
1. DimTable.PeriodID = 202301 , Pass to filter FactTable.Period >= DimTable.PeriodID AND FactTable.Period >= DimTable.PeriodID - 99 And Condition1="X", Summarize values of this Rolling13 periods and save
2. DimTable.PeriodID = 202302 , Pass to filter FactTable.Period >= DimTable.PeriodID AND FactTable.Period >= DimTable.PeriodID - 99 And Condition1="X" , Summarize values of this Rolling13 periods and append
3. DimTable.PeriodID = 202303 , Pass to filter FactTable.Period >= DimTable.PeriodID AND FactTable.Period >= DimTable.PeriodID - 99 And Condition1="X", Summarize values of this Rolling13 periods and append
I created a Batch Macro, but get I am unable to do Step 2. There are repeated values in the output for Store although it is summarized at Store Level. Attached screenshots. Please advise.
@bornita couldn't you do this as an iterative macro with the period increasing by one each loop?
@bornita
I feel maybe we can use the "Running Total" tool or a batch macro.
But you mentioned to calculate Rolling 13 metrics from any given period from a fact table for all historic periods at Store level
So there might be two batch macro?
We can take a closer look if we can have sample input data and expected output?