week on week % change beast mode
date examples are based on writing today - 6th September.
my aim is to create a beastmode, which can ultimately be applied to any metric, which shows the week-on-week % change. this value would be able to be displayed in a table by date(week) or as a KPI card which would be filtered to show data for last week.
the goal is to have the working beastmode for the values which are pre-calculated in the 'period over period' cards. I have attached the vairance bar line example I use - I would like the variance %s shown here as a beastmode value.
example:
a = sum the complete last week revenue (e.g. 26th August - 1st September *Domo weeks run Sunday to Saturday)
b = sum the complete previous to last week revenue (e.g. 19th August - 25th August)
wow%change = sum(a) / sum(b) -1
e.g. 1500 / 1350 - 1 = 11.1%
I found some details on the domo knowledge board but cannot get the formula to work. I get a divide by zero error. example below...
(CASE
WHEN ((year(`activity_date`)=year(curdate())) AND (weekofyear(`activity_date`)=weekofyear(CURDATE())))
THEN `revenue`
END
/
CASE
WHEN (weekofyear(curdate())=1)
THEN (CASE WHEN ((year(`activity_date`)=(year(curdate()) - 1)) AND (weekofyear(`activity_date`)=52))
THEN `revenue` END )
ELSE (CASE WHEN ((year(`activity_date`)=year(curdate())) AND (weekofyear(`activity_date`)=(weekofyear(CURDATE()) -1)))
THEN `revenue` END )
END) -1
Comments
-
Hi @jonathanlooker, the reason you can get a divide by zero error is becuase you have division in your beast mode. A denominator can never equal 0, else Domo throws an error.
My suggestion is you take the entire denominator and apply a case statement up front if the denominator = 0 then the value will be 0. You numerators and denominators can contain additional case statements, you just need one that encompises the entire formula.
CASE WHEN Denominator = 0 THEN 0 ELSE
Numerator
/
Denominator
END
Thanks,
Josh
1 -
@jonathanlooker In addition to what @jstan said about adding a WHEN clause to prevent divide by zero errors, you have to make sure that the date range on your card is big enough to contain all data being compared. For example, if today (9/13/18) I am looking at data for last week Mon-Sun (9/3/18-9/9/18) vs the previous period (8/27/18-9/2/18), my card has to contain data from 8/27/18 and thereafter. Filtering the card for "last 22 days" or "last 3 weeks" should contain all of this data and then update automatically as you head into a new week.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive