ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
1 Day Left! - The Alteryx Community will be temporarily unavailable for a few hours due to implementation of the new SSO experience starting tomorrow at 5pm MDT. Please plan accordingly. For more information, read the blog.

Alteryx Designer Discussions

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

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

JonaV
7 - Meteor

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
11 - Bolide

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. 

arundhuti726_0-1611119227711.png

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

 

 

Qiu
17 - Castor

@JonaV 
Hope I understand your intention correctly.

0120-JonaV.PNG

JonaV
7 - Meteor

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
15 - Aurora

Hi @JonaV ,

 

Would something like this work?

 

AngelosPachis_0-1611265777495.png

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

Labels