Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Excel: Showing Previous month and sort Descending

neromerob
10 - Fireball

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.

8 REPLIES 8
AmalinaH
Alteryx
Alteryx

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
10 - Fireball

Yes. 
here´s the dummy table. 

AmalinaH
Alteryx
Alteryx

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
10 - Fireball

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 

 

input_output.jpg

AmalinaH
Alteryx
Alteryx

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.

 

AmalinaH_1-1656435739306.png

 

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!

 

 

 

neromerob
10 - Fireball

hello @AmalinaH 

 

Thank you very much!, for now it seems that work, the only thing that i don´t know how to do is to make the final output "sort descending" by the last month, the tool that I have only works if i put the value manually. 

AmalinaH
Alteryx
Alteryx

@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!

AmalinaH_1-1656478972792.png

 

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

 

Cheers!

 

 

neromerob
10 - Fireball

It seem to be working thank very much. 

Labels