Comparing Month and Year Data using Beast Modes in Domo
By Mark Snodgrass, Director of Member Data Strategy for National League of Cities (NLC) Mutual Insurance Company
Wednesday, September 21, 2022
Problem Statement: It is important to be able to compare one month or year to another and ensure that they are equal. This requires us to either hide the current month from a card since it isn’t complete, or show the activity in each month through the same point in time. For example, if it is the 15th of the month, just show the first 15 days of activity in each month. We didn’t want to manually adjust cards each month or have to explain why a month looks so different from another month.
Solution Statement: I was able to solve this problem by creating two different beast modes for the two different situations. If I want to exclude the current month from a card, I create a beast mode that looks at the date in the dataset and checks to see if it is less than or equal to the last day of the current date’s previous month. If it is, then I assign it a value of “include”, otherwise I assign it a value of “exclude”. I put this beast mode in my filter and filter to include. Here is the beast mode:
Beast Mode for including completed months
CASE WHEN `EntryDate` <= LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 MONTH))
THEN ‘Include’
ELSE ‘Exclude’
END
If I want to only show activity through the same point in time each month, I utilize the DAYOFMONTH function to evaluate whether the day is less than or equal to today’s day. I then include or exclude and drag it into the filter just like my other beast mode. This allows us to see how the current month is trending compared to the same point in time as previous months. Here is the beast mode:
Beast Mode for including only up to the same point in time of each month
CASE WHEN DAYOFMONTH(`EntryDate`) <= DAYOFMONTH(CURRENT_DATE())
THEN ‘Include’
ELSE ‘Exclude’
END
Demo: I created a video that demonstrates how to use each function:
Share this:
As the director of member data strategy for National League of Cities (NLC) Mutual Insurance Company, Mark helps NLC Mutual members leverage Domo to analyze their claims and underwriting data. With more than 20 years of IT experience, he also serves as chief information officer of a risk-management pool. He was one of the first people to receive Domo’s MajorDomo certification, regularly provides assistance to the Domo community through the Dojo, and is one of only two people to achieve the Dojo’s highest ranking (Black Belt).