Filter tool dynamic replacement

[fedeblanco]

Hi everyone,

I have monthly data for 2 different years and for 2 different accounts. I need to calculate YTD information for each one of the years (2019 and 2020) but I couldn't find an easy way of changing iteratively the values for [Year] and [Month] in the Filter tool (this would be, first 2019|1, 2019|2, 2019|3,.....,2019|12, 2020|1, 2020|2, ...,2020|12). The screenshot below corresponds to the configuration of the Filter tool:

The final summarize tool is just a group by Year.

Does anybody has any idea about how to tackle this?

Thanks!

[Emil_Kos]

Hi @fedeblanco,

Maybe check the running total and just filter the months that you are interested:

The output:

[Emil_Kos]

Hi @fedeblanco,

I edited my previous response as I read once more your request and needed to amend it. 

[fedeblanco]

Hi Emil, many thanks for your comment!

Actually I'm trying to calculate Year to Date information for each month. This is, for example, for March 2020 I need to sum the data for Jan + Feb + Mar, and for October 2020 I need to sum the data for Jan + Feb + Mar + Apr + ... + Oct.

This solution doesn't fit my need but thanks for answering!

[Emil_Kos]

Hi @fedeblanco,

Can you check once more as my solution is showing ytd information. 

I needed to edit my previous response as I didn't get the full picture. 

[fedeblanco]

Hi Emil, thanks again for your answer.

Test data uploaded with my question is a simplified version of the real data I'm dealing with (I can't upload it due to company restrictions). I have another level of data (let's say Country for example) and I need to have 24 output files, one for each combination of year|month. This is why I think the best option would be a dynamic replacement on the filter tool, joined to an output tool (although I'm not being able to do it). I understand your solution and I really appreciate it but it's not what I need for this task.

Thanks again for your time!

[Emil_Kos]

Hi,

I have created a new file for each of the month using the output tool:

There is an option in the output tool to create a new file/sheet based on the column in the data.

In order to achieve it I have created a new column with information about a file name:

Basically engineworkflow directory gives us information on where the workflow is saved(it will create the new files in this directory but you can replace it with C:\Test\ and it will work. 

The output:

It creates excel file for each year month combination:

Please let me know if this is more helpful for you. 

[SeanAdams]

Hey @fedeblanco 

Instead of filtering for every combination of month and year - use the summarize tool to group by the month and year and then it will automatically generate summaries for you for each month.