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

User_32612

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!

GrantSmith

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.

GrantSmith

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

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.

User_32612

@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

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.

User_32612

@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!

User_32612

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!