How to loop over each value of a single column one at a time and pass it to filter

[bornita]

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.

[aatalai]

@bornita couldn't you do this as an iterative macro with the period increasing by one each loop?

[Qiu]

@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?