Dynamically Rearrange Month_Year columns and dynamically filter last 3 Month_Year columns

[mercurial_maverick]

Hello all,

I've a dynamic workflow that refreshes daily and gives the attached output. I want to achieve the below mentioned.

1. I want the Month columns to be dynamically arranged in the correct order (Jan 2020, Feb 2020, ....) and remove outliers (for example: Dec 2021 is an outlier since we are in Jan 2021. It's an error on the backend sql pull where Dec 2020 last week dates are flagged as Dec 2021). Once this is done I want the workflow to proceed to doing the below step.

2. I want to dynamically filter and show last 3 months from current month. For example current month is Jan 2021. I want to show Oct 2020, Nov 2020 and Dec 2020. Similarly when we move into next month (Feb 2021) this needs to filter for Nov 2020, Dec 2020 and Jan 2021.

Please let me know how this can be done.

[AngelosPachis · Accepted solution]

Hi @mercurial_maverick ,

For Q1 I transposed the data and parsed the year and months from the column headers. Then I appended the numeric values for both the year and the month in front of each field, so now when you pivot the table back to the original form, all fields would be sorted properly.

For Q2, I followed a similar methodology, transposing the columns and flagging the 3 prior months to the current month.

Hope that helps, let me know if you have any questions on the workflow.

Regards,

Angelos

[mercurial_maverick]

@AngelosPachis Thank you for your response. Could you please upload only the workflow and not the package? I'm unable to import it from the package.

[AngelosPachis]

No worries @mercurial_maverick ,

I've updated the attachment above, give it a try.

Thanks,

Angelos

[mercurial_maverick]

@AngelosPachis Awesome, that worked. Thank you sir!

[mercurial_maverick]

@AngelosPachis  Hello Angelos! Quick question - Some of the Stores have null values for Last months. For these stores, can we alter the logic to look at previous 3 months and pull the same? For example: A store has Oct = Nov = Dec = 0 but has July, Aug, Sep non zero values. 

For L3M = non zero, pull L3M info.

For L3M = zero, then pull previous 3 months info.

Please let me know how this can be done

[AngelosPachis]

Hi @mercurial_maverick ,

The answer is an iterative macro

and the iterative macro

The catch is that now you are looking for 3 complete months, so even if Aug, Sep contain data but July is Null, the macro will ignore that set of months and will look again 3 months back (Apr, May, Jun)

Let me know if that works for you or if you have any questions on the workflow.

Regards,

Angelos

[mercurial_maverick]

@angelo Thank you for your response. Can we do something like even if one of the 3 months prior to L3M is non null, then bring those 3 months info in?

For example: Oct, Nov, Dec are null. Also, July is null, August is "non null", Sep is null. then bring those 3 months in. Can this be done?

[AngelosPachis]

Yes we can!

I have also copied and pasted the part with the formula tool that will make your months appear in chronological order in your results pane and not alphabetically.

The only change needed was inside the iterative macro, in the summarize tool, instead of looking if all values for each terminal are null, I am now counting how many values are not null. If all of them are empty (all 3 months are empty for a terminal) then I will fetch the prior 3, else even if a single one has a value, this group will be returned.

Hope that helps, let me know if that works for you.

Cheers,

Angelos

[mercurial_maverick]

@AngelosPachis That works, thank you!