30, 60, 90 Day Variance Calculation

Canio

UPDATE:

I have this code, but it isn't doint what I need/want

CASE
WHEN MONTH(`Date`) = 1 THEN SUM(CASE WHEN MONTH(`Date`) = 2 THEN `Revenue` ELSE 0 END)END

What I'm trying to do is say, Ok when the Date is January, grab Feb Revenue and Sum that

I also tried this but it produced nothing:

(CASE WHEN ((year(Date)=year(curdate())) AND (month(Date)=month(CURDATE()))) THEN CY Revenue END
/
CASE WHEN (month(curdate())=1) THEN (CASE WHEN ((year(Date)=(year(curdate()) - 1)) AND (month(date)=12)) THEN CY Revenue END )
ELSE
(CASE WHEN ((year(date)=year(curdate())) AND (month(date)=(month(CURDATE()) -1))) THEN CY Revenue END ) END) -1

Canio

Update: Final:

I was able to solve the riddle using the Variables in a beast mode! Oy vey. Now I'm truly dangerous!

CASE
WHEN MONTH('Select Forecast Date - variable') is not null THEN
SUM(
CASE
WHEN 'Accounting Date' = DATE_ADD(Select 'Forecast Date - variable', INTERVAL -3 MONTH) THEN 'Forecast'
ELSE 0
END
) end

ArborRose

Something like this?

CASE WHEN MONTH(Date) = 1 THEN
SUM(CASE WHEN MONTH(STR_TO_DATE(CONCAT(YEAR(Date), '-02-01'), '%Y-%m-%d')) = 2 THEN Revenue ELSE 0 END)
ELSE 0
END

Check for the month of "date". If it's January, then SUM the contents…which makes a date string and verifies it's month is 2 (February). If so [revenue] else nothing.

Canio

Ok, so I also thought I'd be able to use variables but that is not going to work. I'm just going to build 3 different cards and stick them in the dashboard instead of trying to solve for 30,60,90 in one table.

Canio

@ArborRose The problem is that the data for revenue is on another row, not the same row. So I have to pivot the data some how, or use a variable. But domo doesn't allow variables in that way. I'll have to try to pivot the data somehow

UPDATE: I'm wrong! I understand how to use Variables now. I'm going to update my dashboard and create two date variables and I actually think that will do what I need it to do!

marcel_luthi

I can think of 2 ways to approach this:

ETL and using a window function so you can use LEAD() and have the data pre-aggregated to the Month Level.

Beast Mode since you want to the Sum of February in January you'll need to leverage the FIXED function and write a WHEN statement for each possible month (or at least theoretically). Something like below:

CASE 
WHEN MONTH(`Date`) = 1 THEN AVG(SUM(CASE WHEN DATE_FORMAT(`Date`,'%Y%m') = DATE_FORMAT(`Date`,'%Y02') THEN `Revenue` ELSE 0 END) FIXED())
WHEN MONTH(`Date`) = 2 THEN AVG(SUM(CASE WHEN DATE_FORMAT(`Date`,'%Y%m') = DATE_FORMAT(`Date`,'%Y03') THEN `Revenue` ELSE 0 END) FIXED())

…

WHEN MONTH(`Date`) = 12 THEN AVG(SUM(CASE WHEN DATE_FORMAT(`Date`,'%Y%m') = DATE_FORMAT(DATE_ADD(`Date`,INTERVAL 1 MONTH),'%Y01') THEN `Revenue` ELSE 0 END) FIXED())

END

Be aware that for Month 12, you need to get the number for January next year, so you need the year number displaced by 1.

Canio

Update: Final:

I was able to solve the riddle using the Variables in a beast mode! Oy vey. Now I'm truly dangerous!

CASE
WHEN MONTH('Select Forecast Date - variable') is not null THEN
SUM(
CASE
WHEN 'Accounting Date' = DATE_ADD(Select 'Forecast Date - variable', INTERVAL -3 MONTH) THEN 'Forecast'
ELSE 0
END
) end