Updating month range in iterative macro

Question

Hello everyone! Hope, you're having a great weekend!

I have a set of data with different metrics for every day for the whole year. Each row contains lots of columns with values for one particular day. My goal is to calculate metrics for every month.

At the beginning I have a Filter tool with "If date field is between 01/01/2022 and 01/31/2022", then perform some magic calculation.

Let's use this sample:

As a result of this calculation I get 1 row with all metrics that I need:

So, instead of copy pasting those workflows and adjusting Filter tool for every month, I'm thinking about creating an iterative macro that would go through my data 12 times and produce 12 rows of metrics as an output for every month.

So my question is:

1) How to configure Filter tool in the iterative macro using Action tool to change "date range between filter" for my "date" column?

2) How to put "January", "February", "March" as a first column of each row in my output?

Attaching sample of my workflow. I've tried to simplified it, but in reality it contains lots of formulas, not just summarizing tool with 2 average values.

sample.yxmc

SPetrie · Accepted Answer

You can use the engine iteration number for the filtering instead of an action tool.

You can set the filter to look for the current engine iteration number +1 (since it starts at 0) and only allow dates with that month number through the filter.

tonumber(DateTimeFormat([DateTime_Out],"%m"))=[Engine.IterationNumber]+1

Anything that fails that check, gets passed back to the iterative output to be run through again. The current iteration number will also give you the numerical designation for the month you are running, which can be parsed/transformed back in to full month names for your output.

Be aware that this would not give you expected results if you had January 2022 and January 2021 data being processed at the same time. Your summarize tool would need to group by the year as well to keep the numbers correct. I didnt do that here because you mentioned you were wanting to do just one year and that this is simplified from what you are actually doing.