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

markb
markb Member
in Beast Mode

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.

Tagged:

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited November 2023 Answer ✓

    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)

    If I solved your problem, please select "yes" above

  • markb
    markb Member
    Answer ✓

    This is perfect, it worked! Thank you

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited November 2023

    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.

    If I solved your problem, please select "yes" above

  • markb
    markb Member
    edited November 2023

    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
    ColemenWilson Coach
    edited November 2023 Answer ✓

    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)

    If I solved your problem, please select "yes" above

  • markb
    markb Member
    Answer ✓

    This is perfect, it worked! Thank you

  • markb
    markb Member

    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
    ColemenWilson Coach
    edited November 2023

    + INTERVAL 1 YEAR + INTERVAL 1 DAY

    or

    + INTERVAL 366 DAY

    If I solved your problem, please select "yes" above

Categories