I'm struggling with building a chart to show YOY trends. I was able to use a combination of the following beastmodes to create the following chart from a dataset containing a small amount of data. But when I attempt to do this with a large dataset, it doesn't work (I'm wondering if it just requires too much processing).
Can anyone advise on how to do this better? I'm thinking the extensive case statements is bogging this down.
Is there an easy way to just convert all dates into the last 12 months (so if its 5/7/21, it would be converted to 5/7/23)?
X Axis BM value: Common date (Last 6 mos):
CASE
WHEN EVENT_DATE
>= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 5 MONTH) THEN SUBDATE(EVENT_DATE
, INTERVAL DAYOFMONTH(EVENT_DATE
) DAY)+1
WHEN EVENT_DATE
>= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 17 MONTH)
AND EVENT_DATE
< SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 11 MONTH) THEN ADDDATE(SUBDATE(EVENT_DATE
, INTERVAL DAYOFMONTH(EVENT_DATE
) DAY)+1, INTERVAL 1 YEAR)
WHEN EVENT_DATE
>= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 29 MONTH)
AND EVENT_DATE
< SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 23 MONTH) THEN ADDDATE(SUBDATE(EVENT_DATE
, INTERVAL DAYOFMONTH(EVENT_DATE
) DAY)+1, INTERVAL 2 YEAR)
WHEN EVENT_DATE
>= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 41 MONTH)
AND EVENT_DATE
< SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 35 MONTH) THEN ADDDATE(SUBDATE(EVENT_DATE
, INTERVAL DAYOFMONTH(EVENT_DATE
) DAY)+1, INTERVAL 3 YEAR)
ELSE ''
END
Series BM value: Series (for Asia)
CASE
WHEN event_date
>= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 5 MONTH) THEN 'Last 6 months'
WHEN event_date
>= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 17 MONTH)
AND event_date
< SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 11 MONTH) THEN '1 year ago'
WHEN event_date
>= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 29 MONTH)
AND event_date
< SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 23 MONTH) THEN '2 years ago'
WHEN event_date
>= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 41 MONTH)
AND event_date
< SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 35 MONTH) THEN '3 years ago'
ELSE ''
END