Excel: Showing Previous month and sort Descending

Hello to everyone.

I have a dataset with 4 variables and the rest are months from March 2021 to June 2022, the workflow that I just does two main things.

1)Get rid of duplicate and sum their values by month.

2)Arrange Month in not alphabetical order after the Sum.

But what I need is to show just information about “the previous month” until May if we are in June, until July if we are in June and so on, doing a “Sort Descending” of such month.

This is very difficult for me because the month are supposed to be dynamic and not quite sure how to accomplish that.

Thank you.

Test_Workflow.yxmd

Workflow

Datasets

Input

Reporting

Output

Help

Accepted answers

AmalinaH

@neromerob

This updated workflow sorts the dates by descending. It required having to add a sort and tile tool. The tile tool allows us to number each date in descending order so 1, 2, 3 etc and set these as the column headers. Then we can make use of the dynamic rename tool to rename to the date!

If this solution helps you, please mark it as "Solved" so others can benefit.

Cheers!

Test_Workflow_AH.yxmd

All comments

AmalinaH

Hi @neromerob would you mind sharing the data input .xlsx file so we can have a better understanding of the workflow with the data that you're working with?

neromerob

Yes.
here´s the dummy table.

test_table.xlsx

AmalinaH

Thanks @neromerob , do you have an example of what you would like the end output to look like? What would you like to see in the rows and columns?

Based on what I'm understanding, I'm guessing we can make use of the datetimetoday() function to use today's current month dynamically. However it would be great to have a picture of the end result you would like.

neromerob

Yes.

Hi @AmalinaH

I Add a picture as an Example of the input and output, i try to upload another excel file, but basically this is the idea.

thank you

AmalinaH

Hi @neromerob , would this be what you are looking for?

I created an updated workflow added a filter tool which filters up to before the current month i.e. if we are in June now the filter only takes dates up to May 2022.

On another note, there were some other steps which I did slightly differently as well which I would recommend as well

1. Added select tool to update the data type of SUBCODE from Double to VString

2. Added filter tool to remove any null values in the dataset

3. For dynamic rename, I used the configuration "Take Field Names from Right Input Rows" to dynamically rename the columns from the new title format back to the original date format.

Let me know if this helps!

Test_Workflow_AH.yxmd

AmalinaH

@neromerob

If this solution helps you, please mark it as "Solved" so others can benefit.

Cheers!

Test_Workflow_AH.yxmd

neromerob

It seem to be working thank very much.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703