Alteryx designer Discussions

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

Previous Month Sales formula

Highlighted
7 - Meteor

So as each month passes i send out reports to be analyzed. The bosses like the format of 4 columns

 

YTD Sales vs Last Year Sales

 

then Previous Month Sales vs Last Year Previous Months Sales. I wish to automate these reports so i never have to touch them just run the schedule.

 

I have no issues with the 2 columns for YTD Sales vs Last YTD Sales those are pretty straight forward formulas

 

This issue I come across is the Previous Month Sales Column as this formula works perfect except in January for the new year. Cause in Jan for the new year I want Dec of 2019 sales as my "Previous Month" sales. Any suggestion on how to tweak the below formula to work as I need?

 

iif([year]=datetimeyear(todate(DateTimeNow())) and [Month]=datetimemonth(datetimeadd(todate(DatetimeNow()),-1,"months")),[CE Beer],0)

Highlighted
7 - Meteor

Hi @bdelarosa 

 

Check out the solution in this post - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Formula-to-always-pull-previous-months...

 

I think it might solve your issue

Highlighted
17 - Castor
17 - Castor

hi @bdelarosa 

 

Assuming you've got the sales data with a date field available you can use the following formula

IIf([SalesDate]>=DateTimeAdd(DateTimeTrim(DateTimeToday(),"firstofmonth"),-1,"months") and [SalesDate]<=DateTimeAdd(DateTimeTrim(DateTimeToday(),"lastofmonth"),-1,"months"),[CE Beer],0)

 

This uses [CE Beer] for all the records between the 1st and last days of the previous month and 0 otherwise.

 

Dan

Highlighted
7 - Meteor

yes, that makes sense, i was handicapping myself with the current year formula. I wasn't aware 2020-01-01 minus 1 day would take you back to last year, but that makes total sense now. 

Labels