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

user049141

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?

MarkSnodgrass

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.

user049141

Thanks!

MarkSnodgrass

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