How to Make a Period Over Period Beastmode Work with Date Filtering

user00245

I am trying to create various period over period beastmodes, some for summary numbers and some for the charts themselves. I have variations using currentDate() in the formula - that works on the default view card, but if you add a drill-down or filter by a historical date range they break.

 

Here is a FYTD/FYTD change ($) example. In this case the default view is FYTD, and this works; however, if a user changes the end date via filter, the formula breaks because it still calculates the prior period based on an interval from the current date. I tried changing to Max(`Date`) but that breaks the formula.

 

sum(CASE WHEN (CASE WHEN MONTH(`Date`) > 4 THEN YEAR(`Date`)+1 ELSE YEAR(`Date`) END) = (CASE WHEN MONTH(CURRENT_DATE()) > 4 THEN YEAR(CURRENT_DATE())+1 ELSE YEAR(CURRENT_DATE()) END) THEN `Gross Sell-In Revenue Shipped LC` ELSE 0 END)
-
sum(CASE WHEN `Date` <= DATE_SUB(CURRENT_DATE(),INTERVAL 1 YEAR) AND (CASE WHEN MONTH(`Date`) > 4 THEN YEAR(`Date`)+1 ELSE YEAR(`Date`) END) = (CASE WHEN MONTH(CURRENT_DATE()) > 4 THEN YEAR(CURRENT_DATE())+1 ELSE YEAR(CURRENT_DATE()) END -1) THEN `Gross Sell-In Revenue Shipped LC` ELSE 0 END)

 

AND here is a Last 7 days over prior 7 days beastmode that is intended for a drill down card after you drill into a specific month in the default view. If you drill into the most recent month it works, but for historical months it does not.

 

LEFT(100*
((sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) < 8 THEN `Revenue` ELSE 0 end) /
sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) < 8 THEN `Cost+Fee` ELSE 0 end))
-
(sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) >= 8 AND DATEDIFF(CURRENT_DATE(),date(`Date`)) <= 14 THEN `Revenue` ELSE 0 end)) /
sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) >= 8 AND DATEDIFF(CURRENT_DATE(),date(`Date`)) <= 14 THEN `Cost+Fee` ELSE 0 end))

/

((sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) >= 8 AND DATEDIFF(CURRENT_DATE(),date(`Date`)) <= 14 THEN `Revenue` ELSE 0 end)) /
sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) >= 8 AND DATEDIFF(CURRENT_DATE(),date(`Date`)) <= 14 THEN `Cost+Fee` ELSE 0 end))
,5),

 

Can anyone provide any help? Thanks in advance!

cwolman

I have seen the same problem and could not make it work in beast mode.  I think the only way it would work is if we could pass the current date range selected on the card to the beast mode as a parameter.  

 

I have created dataflows that join the dataset back to itself and create columns for the current year and previous year. This allows for easy variance calculations and charting of variances as well.

 

Hopefully someone else can provide more insight of how to accomplish in a beast mode.

 

Chris

Jarvis

I believe that Chris is correct. It appears that all date filters need to be built into the BeastMode itself and they will not be dynamic. They have some helpful BeastMode examples for different calculations in their Knowledge Base article here:

https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/Sample_Beast_Mode_Calculations%3A_Period-over-Period_Transforms

I know that they built the Period over Period chart types so that they do the dynamic calculations for you. But, they are limited to only a single metric. This is their article on those charts:

https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Building_Each_Chart_Type/Period-over-Period_Charts

I hope this helps. Good luck!

user00245

Thanks, Chris and Jarvis. Yes, unfortunately, the period over period chart types only work for some needs but are too limited for others. I've seen the beastmode help article on period comparisons and currently have similar in place for various summary numbers; unfortunately, they all use (NOW) or CurrentDate, and so they have the same limitations. If I set a summary number to show the % change of the last 30 days/prior 30 days and then someone wants to filter that card to a date range that ended 3 months ago, that summary number becomes inaccurate/breaks. I was hoping someone might have a solution but maybe none exists until Domo enhances beastmode and/or their period/period comparison charts.