Keep previous month and delete others

[Lu26]

Hi all,

I have an excel table with data ordered by month. I now want Alteryx to only keep the column that shows the previous month (e.g. if today is the 3rd of april I only want the data for march) and to delete the other months (but of course keep the first few columns). 

Does anybody know how to do that?

Do I have to use a formula or a filter for that?

I also attached a file to show how the table more or less looks.

Thanks!

[mceleavey · Accepted solution]

Hi @Lu26 ,

I've attached the workflow for you.

Basically, I've isolated the generic columns, 1-4, and the dynamic columns are the ones related to months. I've then pivoted the data, created the calculation for current month-1, joined it to isolate the column that relates to that month, joined it back, and then added it to the generic columns.

Hope this helps,

M.

[Lu26]

Thanks @mceleavey 

It worked perfectly fine!

[mceleavey]

Happy to help!

[Lu26]

Hi @mceleavey ,

to work further with the workflow I need the dynamic column (march etc.) to be in the "double" format (because I need to sort the values later on).

Right now I changed the format with a Select-Tool after the columns were joined at the end of your workflow. But that way it was only possible to do it for the selected month (march). When I tried to change it earlier in the workflow I couldn't keep the headlines and whenever there was text before it changed it to [Null]. That way the workflow didn't work anymore.

The issue is that I need to change all dynamic columns beforehand (not just march) so that Alteryx sorts the values when I do it in the next months the right way.

Do you understand what I mean and happen to know what to do?

Thanks so much!

[mceleavey]

Hi @Lu26 ,

No, sorry, I don't know what you mean.

Can you send me an example of what you need?

M.

[Lu26]

I attached a screenshot to show you what I did so far. 

But I need to change the format to a double for all months, not just march. And if I do it earlier in the workflow for all the other months it deletes the headlines. 

But I need e.g. the april also in a double so if I run the workflow next month it works as well and sorts the values for april in the right order.

[mceleavey]

Ah, ok I see. So it's easy to do manually, but the following month you won't be able to sort by the name of the column as that would have changed.

Ok, try the attached workflow, that should dynamically determine the month and sort accordingly.

M.