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

Domo1.png

Domo2.png

Domo3.pdf

Find more posts tagged with

Graphs

Accepted answers

ST_Superman

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.

All comments

ST_Superman

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`

Bozzuto

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

ST_Superman

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

Bozzuto

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.`
end

FY18

CASE when `Type`='Risk'
then (`FY 2018` * -1)
else `FY 2018`
end

ST_Superman

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.

Bozzuto

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!