MTD and YTD TOGGLE

STEVE T

HI Team,

I have a date field and what I'm trying to do is write a beastmode that looks at YTD and MTD without years. I have successfully done that in 2 separate beastmodes to give my a 'Y' or 'N' toggle. However when I try to combine them into one where the user can toggle 'YTD' or 'MTD' the 'MTD' isn't the issue.. it's my 'YTD' is excluding the current 'MTD'… You with me so far?

Solutions I have tried….

Writing it in a beast mode:

CASE

WHEN DAYOFYEAR(Start/Invoice Date) <= DAYOFYEAR(CURRENT_DATE()) Then 'YTD'
WHEN MONTH(Start/Invoice Date) = MONTH(CURRENT_DATE()) AND DAY(Start/Invoice Date) <= DAY(CURRENT_DATE()) Then 'MTD'
ELSE 'N'
END

I've also gone into ELT and Created 3 Columns in my data…

MTD NUMBER = 1

YTD NUMBER = 1

MTD AND YTD HELPER = (MTD NUMBER + YTD NUMBER) To give me a 1 or 2 value

I then when back to beastmode to write this and it's still giving me the same issues…

CASE
WHEN MTD AND YTD HELPER >= 1 THEN 'YTD'
WHEN MTD AND YTD HELPER = 2 THEN 'MTD'
ELSE 'N'
END

Again, What I'm trying to do is create a Beastmode with a 'YTD' and an 'MTD' Selection that I will turn into a filter for the user to select.

Phoning a friend here… don't let me down! 😁

brycec

CASE statements are exclusive, so once it finds an option that is true, it exits and doesn't evaluate other options. Not sure really how to explain it better, but I do have the solution.

This is what variables are for! :) They are wonderful honestly. Create a variable that has your options of YTD and MTD, then create a Beast Mode like this:

(CASE

  WHEN `Date Filter Variable` = 'MTD' THEN (CASE WHEN MONTH(`Start/Invoice Date`) = MONTH(CURRENT_DATE()) AND DAY(`Start/Invoice Date`) <= DAY(CURRENT_DATE()) THEN 'Yes' ELSE 'No' END)

  WHEN `Date Filter Variable` = 'YTD' THEN (CASE WHEN DAYOFYEAR(`Start/Invoice Date`) <= DAYOFYEAR(CURRENT_DATE()) THEN 'Yes' ELSE 'No' END)
  ELSE 'Yes'

END)

Then add your Beast Mode as a filter (but not a quick filter, as that is what the variable will serve as) and select Yes.

brycec

CASE statements are exclusive, so once it finds an option that is true, it exits and doesn't evaluate other options. Not sure really how to explain it better, but I do have the solution.

This is what variables are for! :) They are wonderful honestly. Create a variable that has your options of YTD and MTD, then create a Beast Mode like this:

(CASE

  WHEN `Date Filter Variable` = 'MTD' THEN (CASE WHEN MONTH(`Start/Invoice Date`) = MONTH(CURRENT_DATE()) AND DAY(`Start/Invoice Date`) <= DAY(CURRENT_DATE()) THEN 'Yes' ELSE 'No' END)

  WHEN `Date Filter Variable` = 'YTD' THEN (CASE WHEN DAYOFYEAR(`Start/Invoice Date`) <= DAYOFYEAR(CURRENT_DATE()) THEN 'Yes' ELSE 'No' END)
  ELSE 'Yes'

END)

Then add your Beast Mode as a filter (but not a quick filter, as that is what the variable will serve as) and select Yes.

STEVE T

FREAKIN GENIUS! EXACTLY WHAT I NEEDED!!!!!! THANK YOU! THANK YOU! THANK YOU!