Creating a Beast Mode for a Tooltip to Compare Data from one Column to Another

Hello,

I have a card that is used to display YoY growth per week for a market. My setup is as follows:

-A clustered bar chart with symbols

-Week # is the x axis

-LY & TY Dollars is the y axis

-YoY Growth % are the symbols which are connected to a secondary axis.

The chart looks great and is displaying everything correctly, however there is one issue. Due to shifts related to Covid, Amazon Prime Day took place in week 42 LY whereas this year it took place in week 26. For this reason, I am trying to create a beast mode which checks whether the current week is week 26, and then displays the % growth vs 42 LY so that I can display that alternative growth rate as a tooltip alongside the 26 TY vs 26 LY growth rate. My Beast Mode is as follows:

case when `Week` = 26 then

CONCAT(

(

SUM(case when `Week` = 26 then 

case when `Year` = 2021 then `Dollars Total` else 0 end 

else 0 end)

SUM(case when `Week` = 42 then 

case when `Year` = 2020 then `Dollars Total` else 0 end 

else 0 end)

)

/

SUM(case when `Week` = 42 then 

case when `Year` = 2020 then `Dollars Total` else 0 end 

else 0 end)

,' ','vs Prime LY')

else '' end


Below is the result:

-Note that the 39% is the true growth rate for week 26 YoY.

The issue I am running into is that the formula shows "Infinity" for week 26 on the chart because it is pulling a zero for week 42. Are there any functions in Beast Mode which enable you to pull in a # from a separate week in this instance other than the one which is related to the column it is set for? Any help is appreciated!

Best Answer

  • GrantSmith
    GrantSmith Indiana 🔴
    Accepted Answer

    This video may provide some insight as to why there's a double aggregation: https://youtu.be/gO8OLpsAk4M?t=1066

    Simply put Domo aggregates the data twice if you have another grouping on the card so you need a double aggregate.

Answers

  • GrantSmith
    GrantSmith Indiana 🔴

    Hi @User_32612

    You could utilize a window function for your denominator (may need to talk with your CSM to turn this on in your instance):


    SUM(SUM(case when `Week` = 42 then
    
    case when `Year` = 2020 then `Dollars Total` else 0 end
    
    else 0 end)) OVER ()
    

    However if Prime Day ever shift again in the future (it will) you have to keep changing the beast mode every year.

    I'd rather recommend utilizing a Date Dimension table (you can utilize Domo Dimensions connector -> Calendar Dates dataset as a starting point). You can use a web form to define the dates and if they are a prime day or not, left join that to your date dimension so you have the flag for each date if it's a prime day or not.

    You can also define different offsets (last week, last month, last year) which would calculate a given date X days/years/months etc ago for each date.

    You can also join your Prime Day dates to each other based on the year and which prime day number so you can have this year and last year's prime day dates on the same row for comparison.


    @jaeW_at_Onyx has a good video on this topic you can find here: https://www.youtube.com/watch?v=CDKNOmKClms&t=176s

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    agree with @GrantSmith , the solution to this problem should be implemented in the structure of the data instead of logic in a beast mode.

    this is probably the kind of thing that you need to be universally true not only true in the card that happens to be using the correct beast mode. "single version of the truth" and all that.

  • @GrantSmith could you explain what that function does differently by adding the extra sum functions & the over() function? I reached out to my CSM and we have window functions enabled but when I used this function it provided an error and the card would not load despite the beast mode viewer validating the function.

    I agree with you both that this fix shouldn't be applied on a card by card basis like this, however this method of analysis will only be used in a limited capacity so I'd prefer to just create a functioning beast mode for this one card rather than modify our data.

  • @GrantSmith I watched the video and the explanation makes sense, though I am still getting "Infinity" as the result of the beast mode when using the window functions. I have a meeting with my CSM today at 4pm to look into this. I will let you know if we have arrived at a solution. Thank you for the assistance so far!

  • It appears the reason the denominator in my beast mode was calculating as zero was because i had week 42 filtered off the chart so domo could not reference it. Problem solved!