YOY calculations

moniP

I saw this post https://dojo.domo.com/t5/Domo-Dojo-Contest-January-2016/quot-Best-Beast-Mode-Formula-quot-Year-over-Year-Change/idi-p/7933

since I was having difficulty with Period over periods. Although useful it only works if you have the data range to select both years worth of data.

 

I am trying to just isolate a week and have a YOY% comparison for a certain dimension column with values 

that existed a year ago but in my card I will only want to see the values for in 2016 with a column YOY%

 

 

 

jaredpk

It looks like at least one problem is with the date field in your second week function, highlighted in red.  Those should be back-ticks (`) and not single quotes.  Please test that and let me know if it works.   If there still is a problem I will often just test one simpler part of the formula to ensure that works and then go on to the rest.  

 

 

(
(SUM(CASE WHEN (YEAR(`Date`)) = YEAR(CURRENT_DATE()) and WEEK('Date') = WEEK(CURDATE()) THEN `Revenue` ELSE 0 END))
-
(SUM(CASE WHEN (DATE_FORMAT(`Date`,'%m/%d')) >= (DATE_FORMAT((CURRENT_DATE()),'%m/%d')) THEN 0 WHEN (YEAR(`Date`)) = (YEAR(CURRENT_DATE())-1) AND WEEK('Date') = WEEK(CURDATE()) THEN `Revenue` ELSE 0 END))

/
(SUM(CASE WHEN (DATE_FORMAT(`Date`,'%m/%d')) > (DATE_FORMAT((CURRENT_DATE()),'%m/%d')) THEN 0 WHEN (YEAR(`Date`)) = (YEAR(CURRENT_DATE())-1) AND WEEK('Date') = WEEK(CURDATE()) THEN `Revenue` ELSE 0 END))

jaredpk

Hi -- Are you trying to compare the current week's performance, with the same week last year?  If so, there is a "Week" function that will show you the week number of the year 0-52.

 

The syntax for current year would be:

case when year(`Date`) = year(curdate()) and Week(`Date`) = week(curdate()) then `Amount` else 0 End

 

The syntax for prior year would be:

case when year(`Date`) = year(curdate()) -1 and Week(`Date`) = week(curdate()) then `Amount` else 0 End

 

Can you clarify if that is what you need?  Thanks,

moniP

ke I am Looks like I am hitting an syntax error

(
(SUM(CASE WHEN (YEAR(`Date`)) = YEAR(CURRENT_DATE()) and WEEK('Date') = WEEK(CURDATE()) THEN `Revenue` ELSE 0 END))
-
(SUM(CASE WHEN (DATE_FORMAT(`Date`,'%m/%d')) >= (DATE_FORMAT((CURRENT_DATE()),'%m/%d')) THEN 0 WHEN (YEAR(`Date`)) = (YEAR(CURRENT_DATE())-1) AND WEEK('Date') = WEEK(CURDATE()) THEN `Revenue` ELSE 0 END))

/
(SUM(CASE WHEN (DATE_FORMAT(`Date`,'%m/%d')) > (DATE_FORMAT((CURRENT_DATE()),'%m/%d')) THEN 0 WHEN (YEAR(`Date`)) = (YEAR(CURRENT_DATE())-1) AND WEEK('Date') = WEEK(CURDATE()) THEN `Revenue` ELSE 0 END))

 

I don't see WEEK in purple

 

Thanks! Appreciate it!

jaredpk

It looks like at least one problem is with the date field in your second week function, highlighted in red.  Those should be back-ticks (`) and not single quotes.  Please test that and let me know if it works.   If there still is a problem I will often just test one simpler part of the formula to ensure that works and then go on to the rest.  

 

 

(
(SUM(CASE WHEN (YEAR(`Date`)) = YEAR(CURRENT_DATE()) and WEEK('Date') = WEEK(CURDATE()) THEN `Revenue` ELSE 0 END))
-
(SUM(CASE WHEN (DATE_FORMAT(`Date`,'%m/%d')) >= (DATE_FORMAT((CURRENT_DATE()),'%m/%d')) THEN 0 WHEN (YEAR(`Date`)) = (YEAR(CURRENT_DATE())-1) AND WEEK('Date') = WEEK(CURDATE()) THEN `Revenue` ELSE 0 END))

/
(SUM(CASE WHEN (DATE_FORMAT(`Date`,'%m/%d')) > (DATE_FORMAT((CURRENT_DATE()),'%m/%d')) THEN 0 WHEN (YEAR(`Date`)) = (YEAR(CURRENT_DATE())-1) AND WEEK('Date') = WEEK(CURDATE()) THEN `Revenue` ELSE 0 END))

Unknown

@moniP, did any of @jaredpk's posts help you out?