Dynamic Date within Beast Mode

AdamD88

I have a Beast Mode that is grouping transactions based on their date:

CASE WHEN OrderHeaderLocalDate >= '02/27/2024' THEN 'Since Changes CY'

  WHEN OrderHeaderLocalDate >= '08/01/2023' AND OrderHeaderLocalDate <= '02/26/2024' THEN 'Prior YTD CY'

  WHEN OrderHeaderLocalDate >= '02/27/2023' AND OrderHeaderLocalDate <= '04/08/2023' THEN 'Since Changes PY'

  WHEN OrderHeaderLocalDate >= '08/01/2022' AND OrderHeaderLocalDate <= '02/26/2023' THEN 'Prior YTD PY'

ELSE 'Not in Scope'

END

This works fine, and I was using another piece of data to figure out a Year to Date period for current and prior year so it was original 07/31/2023 in place of the 04/08/2023, however I have since found the the YTD flag that exists within the data already works in a different way to what I expected, so is including a few more days than I want.

I've tried various iterations of changing the 04/08/2023 (08/04/2023 in UK) to be dynamic and within the beast mode I was getting a validated code, but when using in a visualization no results.

I have tried finding the Max date within OrderHeaderLocalDate - 366 days (leap year), this worked within a beast mode and then visualisation, but within the case seems to be a no go.

Is anyone able to help with a solution to change 04/08/2023 to what is effectively the most recent order within the dataset minus 1 year within the case statement? This means that each day when the dataset is updated with the latest transactions I won't have to make an amend every day!

I'm sure there is a simple solution I am making massively more complicated than it needs to be!

Thanks!

GrantSmith

You can try and utilize a window function for your date instead of '04/08/2023':

MAX(`OrderHeaderLocalDate`) OVER () - INTERVAL 1 YEAR

This will look across your entire dataset and get the maximum date and return it for each row.

AdamD88

Thanks for this Grant, strangely this was allowed, however it didn't bring any data back even though you could see it within the filter, so I'll try a few more things to try and narrow down my issue!