30, 60, 90 Day Variance Calculation

Canio
Canio Member
edited March 19 in Beast Mode

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

image.png

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

Tagged:

Best Answer

  • Canio
    Canio Member
    Answer ✓

    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

Answers

  • ArborRose
    ArborRose Coach

    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.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Canio
    Canio Member

    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
    Canio Member
    edited March 20

    @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
    marcel_luthi Coach

    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
    Canio Member
    Answer ✓

    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

Categories