MTD and YTD TOGGLE

STEVE T
STEVE T Member
in Beast Mode

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

Tagged:

Best Answer

  • brycec
    brycec Contributor
    edited July 1 Answer ✓

    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.

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

Answers

  • brycec
    brycec Contributor
    edited July 1 Answer ✓

    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.

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

  • STEVE T
    STEVE T Member

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

Categories