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

Formula to always pull previous months data

IJH34
8 - Asteroid

I am looking for a formula that will return data from the previous month. I know I could use a simple filter tool and define my dates each month, but I would like to automate the process so it's always looking back at the previous months data. 

 

Example: Today is 6/6/2018, I would like all data from 5/1/2018-5/31/2018

 

In 2 months (8/6/2018), I would like data from 7/1/2018-7/31/2018

 

12 REPLIES 12
Kenda
16 - Nebula
16 - Nebula

@IJH34 Make sure your incoming field is already a date field and try this expression in your Filter tool then keep the T output:

ToNumber(DateTimeMonth(DateTimeToday()))-tonumber(DateTimeMonth([date]))=1
IJH34
8 - Asteroid

@Kenda Close! This also includes data from the current month. I only wants last months data. 

Kenda
16 - Nebula
16 - Nebula

@IJH34 I had realized that just after I posted and actually edited the formula. Try the one in my previous post now.

Nate1
8 - Asteroid

That formula doesn't account for the year of the data so it will take take from the previous month of last year too. It won't work during January either when the current month is 1 - previous month 12. 

 

Here is another formula I tried that should work for those situations.

 

[date] <= ToDate(DateTimeAdd(DateTimeFirstOfMonth(), -1, "days")) 
and [date] >= ToDate(DateTimeAdd(DateTimeFirstOfMonth(), -1, "months"))

 

IJH34
8 - Asteroid

This does the trick! Thank you so much!

zwiskur
6 - Meteoroid
What is ToDate stands for? It says error
zwiskur
6 - Meteoroid
I have the field [date closed] that has dates in it. I need to have the formula to pull only the previous month. As today is 07/28/2018 I need data 06/01/2018-06/30/2018. When I put the formula [Date Closed] <=ToDate(DateTimeAdd(DateTimeFirstOfMonth(),-1,”days”)) and ToDate >=(DateTimeAdd(DateTimeFirstOfMonth(),-1,”month”)) The error comes : unknown function ToDate
Kenda
16 - Nebula
16 - Nebula
zwiskur
6 - Meteoroid
Thank you!
Labels