Beast Mode: How to aggregate a value for 1yr and remainder of the yr from a date?

markb · 2023-11-06T20:10:59+00:00

There was an error rendering this rich post.

Hi - I am trying to figure out how to aggregate values from a run date thru the end of the year as well as aggregating from that same date as_of_date for 1 year out.

Essentially if the date is 5/31/23 it'll aggregate a value from that date through the end of the year (12/31/YY). Separately, from that same as of date (5/31/23) aggregate the value through the following year to 5/30/24. The as of dates are always changing so I'm trying to get the beast mode to pick the dates up correctly.

Find more posts tagged with

Date

Beast Mode

Accepted answers

ColemenWilson

Gotcha! So then you would do something like this:

For the Date to the end of the year:
SUM(CASE WHEN `dateField` >= `as_of_date` AND `dateField` <= LAST_DAY(CURRENT_DATE() + INTERVAL (12 - MONTH(CURRENT_DATE())) MONTH) THEN `values` END)

For the date to 1 year from that date:
SUM(CASE WHEN `dateField` >= `as_of_date` AND `dateField` < `as_of_date` + INTERVAL 1 YEAR THEN `values` END)

markb

This is perfect, it worked! Thank you

All comments

ColemenWilson

SUM(CASE WHEN `dateField` >= '2023-05-31' AND `dateField` <= '2023-12-31' THEN `values` END)

SUM(CASE WHEN `dateField` >= '2023-05-31' AND `dateField` <= '2024-05-30' THEN `values` END)

If the "as of date" is fixed and available somewhere that can be pulled into the dataset then the beastmodes would be a bit different, but without that you'd have to use the beastmodes above. If the "as of date" is always the current date (which it doesn't seem like it is) then you could use CURRENT_DATE() in your beastmode instead and then use INTERVAL logic to get the dates relative to the current date.

markb

Hi - Thanks for the help. To give more clarity the as_of_date is a column in the dataset for ~100 rows per day with every day being an option(daily occurrence). The example of 5/31/23 was just one instance of this. I'm trying to get the beast mode to calc the remainder of the current calendar year and a rolling one year based on an as_of_date that can be any date. So the if it was from 7/31/23 it'd go to 7/30/24. Trying to avoid hardcoding in dates. Everything in the report is based off the 'as of date' and looking at the horizons

ColemenWilson

Gotcha! So then you would do something like this:

For the Date to the end of the year:
SUM(CASE WHEN `dateField` >= `as_of_date` AND `dateField` <= LAST_DAY(CURRENT_DATE() + INTERVAL (12 - MONTH(CURRENT_DATE())) MONTH) THEN `values` END)

For the date to 1 year from that date:
SUM(CASE WHEN `dateField` >= `as_of_date` AND `dateField` < `as_of_date` + INTERVAL 1 YEAR THEN `values` END)

markb

This is perfect, it worked! Thank you

markb

How would I add one more day to the 1 year equation? I tried these without success

'+ INTERVAL 1 YEAR + 1 Day'

'+ INTERVAL 1 YEAR and 1 Day'

'+ INTERVAL 1 YEAR 1 Day'

ColemenWilson

+ INTERVAL 1 YEAR + INTERVAL 1 DAY

+ INTERVAL 366 DAY