How to ignore one month from a date by day ytd column

[JonaV]

Hi,

I want to get some ideas on how to ignore the latest month from a date column that has dates from 1/1/2020 to 12/31/2020 by day. So, if I am reporting for November YTD (meaning 1/1/2020 to 11/30/2020) and I have data from 1/1/2020 to 12/31/2020, I want to ignore the data from 12/01/2020 to 12/31/2020.

What I want to accomplish with this is to see the amount of resources we had for every prior month using this YTD file. (if possible without having to use any other tools than the formula tool)

[Tyro_abc]

Hi @JonaV 

From Date, derive month ---> Summarize by Month ----> Get running total by Month -----> Substract Current Month from Running total.

Approach would depend on your data structure, I have attached a workflow just for sample. 

Please note, I used random function to generate data, so every time you run the flow, result would be different.

[Qiu]

@JonaV 
Hope I understand your intention correctly.

[JonaV]

I want this to change automatically every month instead of me having to type in the month number/name

So, I want Alteryx to look at the max month date, in this instance is 12/31/2020, and subtract one month to only look at dates before 12/01/2020. So, if I were using a filter tool, the formula would be [Date] < "12/01/2020", but I don't want to go in and type in the date manually. I want the date to automatically change to the previous month, so next month the date would change to "01/01/2021" 

[AngelosPachis · Accepted solution]

Hi @JonaV ,

Would something like this work?

It gets the maximum date in your dataset, subtracts one months and finds which dates you have to keep and then applies the filter.