YOY calculations

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%

Find more posts tagged with

Accepted answers

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))

All comments

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

I don't see WEEK in purple

Thanks! Appreciate it!

jaredpk

unknown

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