Graph Display - Suggestions/Comments
Hello,
Please see attached screenshots. I have risk & opportunity for 2 different groups, FY18 and MS/DS Prediction. Is there a way I can group these differently to show up in the graph the way I want? I know whe have to take out Risk & Opportunity from the X axis because that is what is currently grouping them. But I would like FY18 (Blue bars) for risk & opportunity grouped next to each other, therfore I guess making FY18 and MAS/DAS Prediction as the X axis categories. The negative bars represent the risk and the positive bars represent the opportunity. However, I want the opportunity to be side by side with the risk, not how it currently is (both opportunities side by side). So i'm not sure what I could put as the category, because I can't put type (Which gives me Risk and Opportunity on the x axis) for it won't group them the way I want them to be.
Then I want a third bar for each group, which displays the difference between risk and opportunity, but grouped with it's correct categories (FY18 and MAS/DAS Prediction). Please see Domo3.pdf for drawing of what I would like (Top drawing).
Do I use SQL for this? What's the best approach? Sorry I know this was a terrible explanation of what I want
Best Answer
-
Then I think this would work to stack up the data for you in a MySQL data flow:
select
sum(case when `Type`='Opportunity' then `FY 2018` end) as `Opportunity`
,sum(case when `Type`='Risk' then (`FY 2018` * -1) end) as `Risk`
,'FY18' as `Category`
FROM risk_opportunities
UNION ALL
select
sum(case when `Type`='Opportunity' then `MS/DS Pred.` end) as `Opportunity`
,sum(case when `Type`='Risk' then (`MS/DS Pred.` * -1) end) as 'Risk'
,'MAS/DAS Prediction' as `Category`
FROM risk_opportunities
This will give you a new table with 3 Fields: Opportunity, Risk, and Category. Use that table as a new output data set and you should be able to make the visualization you were looking for.
1
Answers
-
I would use MySQL for this issue. My understanding is that you have two different data sets. One for FY18 opp and risk; and one for predicted opp and risk.
You will want to stack these two data sets into a single data set.
SELECT
fy.`opp` AS `Opportunity`,
fy.`risk` AS `Risk`,
(fy.`opp` + fy.`risk`) AS `Difference`,
'FY18' as `Category`
FROM fiscal_year_18_table fy
UNION ALL
SELECT
p.`opp` AS `Opportunity`,
p.`risk` AS `Risk`,
(p.`opp` + p.`risk`) as `Difference`,
'MS/DS' as `Category`
FROM ms_ds_prediction_table p
This will stack your data up for you. You would then graph Category across the x axis with the other categories as series.
This is also assuming that your risk field is negative. If risk is positive, you will need to adjust the select statements to something like this:
(0 - fy.`risk') AS `Risk`
0 -
Hey Scott,
Thanks for the help! I tried what you gave me and it doesn't recognize the tables. I'm only using one data set (Risk_Opportunities) so I tried to use that instead since i know that table exists, but it still didn't work. I played around with what you gave me for a while, putting in different commands and altering the code, but I still wasn't able to figure it out. Do you have any other suggestions? I'm stumped and you seem more advanced then me
0 -
Thanks for the response. I was under the assumption that your risk and opportunity for the two different groups was coming from two different data sets. Looking at the pics you shared in the original post, it looks like the fields that you are wanting to work with are from a beast mode as well. Would you mind sharing the BeastMode?
In order to stack the columns appropriately for the visualization that you want, you will need to do this calculation in the MySQL data flow.
It would look something like this:
select
sum(case when `Type`='Opportunity' then {FY18 beastmode} end) as `Opportunity`
,sum(case when `Type`='Risk' then {FY18 beastmode} end) as `Risk`
,'FY18' as `Category`
FROM risk_opportunities
UNION ALL
select
sum(case when `Type`='Opportunity' then {MAS/DAS Prediction beastmode} end) as `Opportunity`
,sum(case when `Type`='Risk' then {MAS/DAS Prediction beastmode} end) as 'Risk'
,'MAS/DAS Prediction' as `Category`
FROM risk_opportunities
I believe you could then make a beast mode with this output table that would give you the difference:
`Opportunity` + `Risk`
(if `Risk` is negative)
You would use a grouped bar graph with Category as the x-axis and Opportunity, Risk, Difference as the value/series fields
0 -
Sure thing. The beastmode calculations are just there to say if it is a Risk then the output should be negative. Here are my beastmode calculations:
MAS/DAS Prediction
CASE when `Type`='Risk'
then (`MS/DS Pred.` * -1)
else `MS/DS Pred.`
endFY18
CASE when `Type`='Risk'
then (`FY 2018` * -1)
else `FY 2018`
end0 -
Then I think this would work to stack up the data for you in a MySQL data flow:
select
sum(case when `Type`='Opportunity' then `FY 2018` end) as `Opportunity`
,sum(case when `Type`='Risk' then (`FY 2018` * -1) end) as `Risk`
,'FY18' as `Category`
FROM risk_opportunities
UNION ALL
select
sum(case when `Type`='Opportunity' then `MS/DS Pred.` end) as `Opportunity`
,sum(case when `Type`='Risk' then (`MS/DS Pred.` * -1) end) as 'Risk'
,'MAS/DAS Prediction' as `Category`
FROM risk_opportunities
This will give you a new table with 3 Fields: Opportunity, Risk, and Category. Use that table as a new output data set and you should be able to make the visualization you were looking for.
1 -
That worked! You're like a wizard Scott! I then made the beast mode like you said for the Total Risk & Opportunity bar and that worked too. Thanks alot!
2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive