MTD and YTD TOGGLE

STEVE T · 2024-07-01T22:01:54+00:00

There was an error rendering this rich post.

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! 😁

Find more posts tagged with

Filters

Magic ETL

Beast Mode

Accepted answers

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.

All comments

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!

Data_Devon

@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

@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.