What's wrong with this case when statement / how do case when's operate on the back end?

user049141
user049141 Member
in Magic ETL

Hello! I'm having a problem with a case when statement, and it seems like its not working because I don't fully understand how/when Case When's output information, especially when nested. Here's the code for my new variable `CurrentWeekofYear`

case when `Week Number` = (case when `WorkDate`=TODAY() then `Week Number` end) then 'Current Week' /*equals --current week number*/
   else 'Not Current Week' end

I have a variable, `Week Number` , where I have the week of the year that the variable WorkDate falls under. I want my beast mode to check each record, and see if the week number of that record is the same week number of the current day, and then output 'Current Week' if the statement is true, and 'Not Current Week' if the statement is not true.

However, with the code I have, I only get results if 'Current Week' when the WorkDate is the same as today's current date. The other days of the same week still say 'Not Current Week' (I inserted a fake example below, with the mislabeled row highlighted) Can someone help me understand why this is happening? And maybe point me to a resource where I can can better understand how/when case when statements operate?

image.png


Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    The issue is because your function is only going to return the week number variable when work date equals today. You can do this much simpler by using this as your beast mode:

    case when `Week Number` = WEEKOFYEAR(CURRENT_DATE()) then 'Current Week'
else 'Not Current Week' end

      

      You could also choose to use your workdate field instead of your weeknumber field by changing your beast mode to this:

    case when WEEKOFYEAR(`WorkDate`) = WEEKOFYEAR(CURRENT_DATE()) then 'Current Week'
else 'Not Current Week' end

      

      You can use whichever is easier for you to read.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    The issue is because your function is only going to return the week number variable when work date equals today. You can do this much simpler by using this as your beast mode:

    case when `Week Number` = WEEKOFYEAR(CURRENT_DATE()) then 'Current Week'
else 'Not Current Week' end

      

      You could also choose to use your workdate field instead of your weeknumber field by changing your beast mode to this:

    case when WEEKOFYEAR(`WorkDate`) = WEEKOFYEAR(CURRENT_DATE()) then 'Current Week'
else 'Not Current Week' end

      

      You can use whichever is easier for you to read.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • user049141
    user049141 Member

    Thanks!

  • MarkSnodgrass
    MarkSnodgrass Coach

    No problem. If you can mark my answer as accepted, that will help others in the community.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories