Percent change (WoW, MoM, etc) as a trending line in card.

EJRM

I'm trying to create a beastmode that could be used in a card to show WoW/MoM/QoQ changes on a trending line. I'm picturing a 'Line + Grouped Bar' graph that would show the actual number value (bar) as well as the percent increase/decrease for each datapoint (line). We've been successful in creating percent change beastmodes as a summary number, but that is static for specific months. I'd like to see the historical trend within the card. Can anyone help?

PodiumMason

Hey EJRM!

 

I went ahead and put together some beast modes that I hope will be helpful for you. My test data is at the month granularity, but the principles should work regardless of what date grain your data is at.

 

Date Alignment: (This is to align the dates from last year and this year, so if you filter the graph to only this year, you don't lose any data. Basically turns 2016-01-01 into 2017-01-01)

 

Case when year(`Date`) = YEAR(CURRENT_DATE())-1 then DATE_ADD(`Date`, Interval 1 Year)
Else `Date` END

 

This Years Sales: (This is to be used in the bar in the bar+line graph, since we aligned the dates, we don't want to count last years sales as this year's sales, so this excludes last year by date)

 

SUM(Case when YEAR(`Date`) = year(CURRENT_DATE()) then `Sales` end)

 

(Optional) Last Years Sales: (In case you want to see last year sales alongside this year with a percentage change line. 

 

SUM(Case when year(`Date`) = YEAR(CURRENT_DATE()) -1 then `Sales` END)

 

YoY % Change: (Calculation This year - Last year / Last Year)

 

(SUM(Case when year(`Date`) = year(CURRENT_DATE()) then `Sales` END)

-

SUM(Case when year(`Date`) = (year(CURRENT_DATE())-1) then `Sales` END))

/

(SUM(Case when year(`Date`) = (year(CURRENT_DATE())-1) then `Sales` END))

 

Screenshot of final product:

 

 

Hope this is helpful! Let me know if you need additional clarification or if you have additional questions.

PodiumMason

Also, the knowledge base has a lot of YoY and QoQ and MoM examples: 

 

http://knowledge.domo.com?cid=beastmodecomparison

 

The Dojo also has some good questions and answers from the past that could be helpful as well, feel free to search past questions. 

user12150

What card type did you use?