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.

Example.xlsx

AngelosPachis

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

example.yxmd