YOY calculations - DOMO recommended formulas not working

ADerbak
ADerbak Member
in Charting

I found this page for doing period-over-period calculations:

 

https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/List_of_Beast_Mode_Comparison_Calculations

 

However, the one I really want is a YoY calculation for the same series, but when I try that formula, it comes back invalid:

 

DOMO YoY Calculation

Case year(`Datefield`) when year(curdate()) then 'This Year' when year(curdate())-1 then 'Last Year’ End

 

Updated for my data:

Case year(`FULL_DATE`) when year(curdate()) then 'This Year' when year(curdate())-1 then 'Last Year' End

 

Two things I noted:

1.) There is a single quote in the original formula that doesn't get recognized properly by Beast Mode (the last single quote).

2.) I have never seen a Case When statement where a criteria was called between the Case and When. I am relatively new to this, so it might just be my lack of experience.

 

Any help would be appreciated!

 

Thank you,

AD

Best Answer

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

     

    Try it like this:

     

     

    Case When year(`FULL_DATE`) = year(curdate()) then 'This Year'

              When year(`FULL_DATE`) =  year(curdate())-1 then 'Last Year' End

     

    Hope it helps.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.

Answers

  • swagner
    swagner Contributor

    @ADerbak

     

    Here's an example of a beast mode I am using to calculate sales PYTD:

     

    sum(

    case when `Invoice Date` <= DATE_SUB(DATE_SUB(CURDATE(), interval (DAYOFWEEK(CURDATE())) day), interval 1 year)
        and YEAR(`Invoice Date`) = YEAR(DATE_SUB(DATE_SUB(CURDATE(), interval (DAYOFWEEK(CURDATE())) day), interval 1 year))
        then `Extended Price` else 0
    end

    )

     

    Notice that the case statement is nested within a sum statement.  Ultimately that's what I'm needing to do, provide a sales total for prior year at the same point in time. 

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

     

    Try it like this:

     

     

    Case When year(`FULL_DATE`) = year(curdate()) then 'This Year'

              When year(`FULL_DATE`) =  year(curdate())-1 then 'Last Year' End

     

    Hope it helps.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
This discussion has been closed.

Categories