Beast Mode Calculation for Yesterday

user069636

I am trying to get a % Change of Revenue between Yesterday and Same day last year. My beast mode formula was:

(SUM(CASE WHEN `Date` = CURDATE() - 1 THEN (`Revenue`) END)
-
SUM(CASE WHEN `Date` = SUBDATE(CURDATE()-1, INTERVAL 52 WEEK) THEN (`Revenue`) END))
/
SUM(CASE WHEN `Date` = SUBDATE(CURDATE()-1, INTERVAL 52 WEEK) THEN (`Revenue`) END)

But I was not getting an accurate result. So I checked to see if at least my Yesterday's revenue was correct

SUM(CASE WHEN `Date` = CURDATE() - 1 THEN (`Revenue`) END)

But nothing displayed for my beast mode calculation.

Is there a false logic with my beast mode calculation?

GrantSmith

Hi @user069636

Whenever I typically need to do some period over period comparisons I actually restructure my data so that I have a report date and a "comparison date" (the actual date the metric was recorded on) and calculate it as necessary. This allows you to have custom defined periods (like 2 weeks ago and a week ago).

 

You'd essentially use the report date in your graph but then have beast modes to determine which type of period to display (last week, 2 weeks ago etc.) and centralizes this logic so you have one place to maintain it rather than across multiple cards and beast modes.

 

I've written about this method previously here: https://dojo.domo.com/t5/Card-Building/show-percent-on-period-over-period-graph/m-p/50540/highlight/true#M7313

Regarding your actual beast mode -

Is your Date field a Date or a Timestamp?

Have you just put in CURDATE() - 1 to see what date it returns to make sure it's returning what you expect?

When I have issues with my beast mode utilize a table card and break down each element of the beast mode into their own beast mode to make sure each step is what I'm expecting. In your case Date in one column and CURDATE() - 1 as a separate beast mode and make sure they line up to what you're expecting.

jaeW_at_Onyx

In your screenshot you've applied the filter for yesterday. (top right corner)

if your data is limited to yesterday then you cannot include / consider the data from one year ago. so when you divide by null you get null.

user069636

I applied the filter for yesterday to see if my beast mode calculation for revenue yesterday was the same as the Revenue data from the dataset. It turns out that there was an issue with my dataset. It is now resolved now. Thank you for your comments!