Sort automatically change the column it selects based on previous month

[maygross]

Hi,

I have this workflow where I want the sort to be changed every month to the previous month. The data is a list of our top customers and their respective revenues for the past six months. The goal is for the sort to show descending revenues for the prior month (ex. we are now in March and I want it to sort by Feb revenues). I plan to post this workflow to the gallery and fully automate it and have it send automatic emails with the output, and therefore I don't want to manually change the sort month every time. The columns after my cross tab tool are customer name and all the months. So I would ideally need a way to automate the column I would sort every time this runs. Please let me know if you have any suggestions.

[SPetrie · Accepted solution]

I feel like there is a better way to do this, but I came up with one method.

Use a dynamic rename to find the column with the previous months date and rename it to "Sort" or something unique that wont affect any other column data. Set your sort up to use that column name.

Then use another dynamic rename to change the "Sort" name back to the original.

[maygross · Accepted solution]

I have never used the dynamic rename tool before, what do I need to set up as the setting here? Just FYI, my fields will change every month depending on the past six months (now its 202210-202303, next month itll be 202211-202304), not sure if this impacts anything but I just wanted to point it out. 

[maygross]

While we're at it, also, how do I make my filter dynamic to only show the past six months. Currently, my flow has 202210-202312 in the input data query so that is why it only shows six months, but how do I limit it to just be the past six months so my output doesnt have too many columns. 

[SPetrie · Accepted solution]

Changing columns should not be an issue.

For the select, select 'all' in the configuration.

Make sure it also has Dynamic or Unknown fields selected too.

Make sure to do that on the second rename as well.

Thats how we are telling the tool what columns we want it to act upon and to also include/exclude new columns or ones that dont exist anymore.

The formula will take the date that the workflow is run on and subtract one month from it and then format it to match YYYYMM.

if [_CurrentField_] = datetimeformat(datetimeadd(datetimetoday(),-1,"Month"),"%Y%m") then "Sort" else [_CurrentField_] endif

(My apologies for not including this last time to make it easier on you. Looks you you missed the format part of the expression which is why its complaining.)

No matter what day you run it in march, it will yield 202302.

When you run it in April, it will then give you 202303.

The formula in the dynamic rename will put that date up against all the column names and only rename the one that matches.

The second rename formula does the revers. It looks for a column named sort and then renames it to the date we formatted YYYYMM

earlier to undo its rename.

if [_CurrentField_] = "Sort" then datetimeformat(datetimeadd(datetimetoday(),-1,"Month"),"%Y%m") else [_CurrentField_] endif

[SPetrie]

For limiting to just the last 6 months, there are a few way it could possible go.

I dont use inDB so not sure if you could alter your query to simply limit time frame there or not.

If its just this branch we are concerned with, using a filter between summarize and the transpose may do it. What formula you need would depend on what the data looks like. 

Assuming you have a "date" column you could use a filter with formula 

datetimeparse([date],"%y%m") >= datetimeadd(DateTimeFirstOfMonth(),-6,"Months")

The formula takes a string with a date formatted as  YYYYMM and creates a datetime data type from it. It then compares it to the date that is 6 months prior to the first of the month when the workflow is run.

Any date 202209 and greater would be allowed through the formula if run in March. 202210 if run in April, etc.

Then the crosstab would act upon the remaining data and those pervious months should not become columns.

A similar formula can be used with a dynamic select after the crosstab as well as another option.

Edited to add an example workflow that has the filter and the select methods in it. The text input I use is taking the place of the summarize tool I saw in your flow.