Grouped Running Total Bar Card where Each BarValue Consists of a Percentage of a Total in Beastmode.
I am having difficulty making a particular card to work.
I am attempting to make a Grouped Running Total Bar card. But instead of number values... I want the bar to represent a percentage to a total of value in particular fields.
There is one text field called Preference_Type and it contains a '1' or a '2' value, denoting Opt-Ins or Opt-Outs. I want an Opt-Out percentage of every running totals months for each `Program_id`.
So...
Total Opt-Outs/Total Preferences(Opt-Ins+Opt-Outs)
I added a `Count` field to simplify the beastmode and try various ways to make the calculation... but none of it worked. Bizarrely I would often get different graphs, even when the beastmode code essentially did the exact same thing but expressed differently.
This is an example of my Beastmode calculation...fyi, I changed the Preference_Type field to text to make this easier.
SUM((case when `Preference_Type`= '1' then `Count`*0 when `Preference_Type`='2' then `Count`*1 else `Count`*0 end) / (`Count`))
In theory, this should, for every week, take the totals of each filter's program's Opt-Outs and divide them by the total Count in that field!!!
I notice the first week is accurate, but the second week doubles the necessary Opt-Outs, and I have no idea what is going on in the thrid month, just wildly innaccurate. I know the formula is accurate because it works in an all time view that is not running total.
Does the running total graph simply just not have this kind of functionality? Does anyone have any Ideas I can do to work around this. I tried magic ETL extensively, but ultimately was unsuccessful.
Best Answer
-
You are spot on, Domo's running total visual won't do this for you unfortunately. It just adds current month to previous months over and over again which is why your first month is right but it goes sideways from there.
This is an inelegant solution, but I was able to get it to work using your example data.
On a Grouped Bar chart I used Column 2 as the Category.
And then beastmodes for individual months:
Jan Beastmode
SUM(CASE WHEN `Preference` = 'Opt-Out' AND MONTH(`Date`) < 2 THEN `Count` ELSE 0 END) / SUM(CASE WHEN MONTH(`Date`) < 2 THEN `Count` END)
Feb Beastmode
SUM(CASE WHEN `Preference` = 'Opt-Out' AND MONTH(`Date`) < 3 THEN `Count` ELSE 0 END) / SUM(CASE WHEN MONTH(`Date`) < 3 THEN `Count` END)
Mar Beastmode
SUM(CASE WHEN `Preference` = 'Opt-Out' AND MONTH(`Date`) < 4 THEN `Count` ELSE 0 END) / SUM(CASE WHEN MONTH(`Date`) < 4 THEN `Count` END)
See the attached screenshot for the output.
Hope that works
Domo Consultant
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
Hi @mrrm17, can you upload (or type out) a short example of your dataset and intended outcomes? You've done a great job of decribing it, it would help me to see examples using values to fully comprehend.
Domo Consultant
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Thank you for the reply!!! I created a mock data table.
Date Program Preference Count
1/1/2018 Duck Opt-In 1 1/1/2018 Goose Opt-In 2 1/1/2018 Thrasher Opt-In 5 1/1/2018 Duck Opt-Out 2 1/2/2018 Goose Opt-In 1 1/3/2018 Goose Opt-Out 2 2/1/2018 Duck Opt-Out 3 2/2/2018 Thrasher Opt-Out 1 2/3/2018 Duck Opt-Out 7 2/4/2018 Goose Opt-In 1 2/5/2018 Thrasher Opt-In 5 3/1/2018 Duck Opt-Out 2 3/1/2018 Goose Opt-Out 1 3/3/2018 Thrasher Opt-Out 2 So I want to use a Grouped Running Total Graph that takes the Opt-Out Counts and divide them with the total running total count for that month.
So I would apply a beastmode...
SUM(case when
`Preference`= 'Opt-Out' then `Count`*1
else `Count`*0
end)
/
SUM(`Count`)
I then separate the running total grouped bar by program and by month. In theory, this calculation should filter by each program and put the running total Count of the particular program in the denominator. I believe this may be where DOMO messes up... I think they are not allowing the denominator be a running total here.
The result should be...
Month Duck Goose Thrasher Jan-18 66.7% 40.0% 0.0% Feb-18 92.3% 33.3% 54.5% Mar-18 93.3% 42.9% 61.5% Thrasher starts out as 0 Opt-Outs but 5 Opt-Ins... 0/5= 0% . Next month it adds 6 Opt-Outs and 0 Opt-Ins...6/11= 54.5%. The denominator and numerator are running totals. Third month Thrasher gains 2 Opt-Outs... 8/13= 61.5%.
0 -
You are spot on, Domo's running total visual won't do this for you unfortunately. It just adds current month to previous months over and over again which is why your first month is right but it goes sideways from there.
This is an inelegant solution, but I was able to get it to work using your example data.
On a Grouped Bar chart I used Column 2 as the Category.
And then beastmodes for individual months:
Jan Beastmode
SUM(CASE WHEN `Preference` = 'Opt-Out' AND MONTH(`Date`) < 2 THEN `Count` ELSE 0 END) / SUM(CASE WHEN MONTH(`Date`) < 2 THEN `Count` END)
Feb Beastmode
SUM(CASE WHEN `Preference` = 'Opt-Out' AND MONTH(`Date`) < 3 THEN `Count` ELSE 0 END) / SUM(CASE WHEN MONTH(`Date`) < 3 THEN `Count` END)
Mar Beastmode
SUM(CASE WHEN `Preference` = 'Opt-Out' AND MONTH(`Date`) < 4 THEN `Count` ELSE 0 END) / SUM(CASE WHEN MONTH(`Date`) < 4 THEN `Count` END)
See the attached screenshot for the output.
Hope that works
Domo Consultant
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Thank you, That'll have to do!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive