beast mode for latest 12 months

user033690

Hi

I need help creating a beast mode to show monthly gross rev for the latest 12 months. I have example on how I get my YTD gross rev below.  I not sure if it can be dome in beast mode or any other way. Your help will be appreciated.  Thank you in advance. 

 

SUM(CASE WHEN `Year`= `Year for Today` AND `Month #` <=`Month # for Today`
THEN `Gross Revenue`
ELSE 0
END)

 

GrantSmith

Hi @user033690 

 

You can utilize some built in functions to get the current date and do some addition / subtractions.

 

CURRENT_DATE() will get you the current date

MONTH() and YEAR() returns the month and year of the date respectively.

 

We can do some math trickery to make the YEAR and MONTH a digit and compare the two values together.

 

 

 

 

CASE WHEN 
`Year` * 100 + `Month #`
<=
YEAR(CURDATE() - INTERVAL '12' MONTH) * 100 + MONTH(CURDATE() - INTERVAL '12' MONTH)
THEN `Gross Revenue`
ELSE 0
END

 

 

 

 

You'll get a number like 201910 and compare it to 202010 which is greater or equal and return the Gross Revenue.

 

This is assuming you're wanting to include the current month from last year (which would be a little over 12 months)

GrantSmith

If you're using a specific date field instead of the current date you could format it with the same structure like:

 

CASE WHEN 
`Year` * 100 + `Month #`
<=
`Year For Today` * 100 + `Month # For Today`
THEN `Gross Revenue`
ELSE 0
END

MarkSnodgrass

@GrantSmith  's answers are spot on as usual. I think it is worth pointing out that you can use the date filter to only pull the last 12 months and that would save you the work of the beast mode, depending on what you are trying to do. Thought I would mention it as some people forget that it is there and all the options it provides without having to do anything.