MTD and YTD TOGGLE

STEVE T · July 2024

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 · July 2024

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 · July 2024

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 · July 2024

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

Data_Devon · March 31

@brycec This has been resurrected and your help is needed again….

What's the purpose of the "yes" or "no" in the beastmode that references the Variable? I don't understand that section of the SQL snippet

brycec · March 31

@Data_Devon,

When you add the Beast Mode as a filter on the card, you need to select and apply its value as 'Yes'. Then, when the variable value is set to 'MTD', for example, the CASE statement then evaluates the second CASE statement on that line. When true, it will produce 'Yes', which matches the value you selected in the filter, and those rows will be included. When false, it will produce 'No', which doesn't match the value you selected in the filter, and those rows will not be included aka filtered out.

Basically, it is just how you combine a Variable and a Beast Mode to make a filter.

MTD and YTD TOGGLE

Best Answer

Answers

Categories