How to use Line + Stacked Bar Chart

Hi Dojos,

Looking for some advice on how to use a Line + Stacked Bar Chart in a specific way.

Currently my chart looks like this:

Screen Shot 2019-10-16 at 12.51.42 pm.png

I want to add a line which indicates the number of clients by region. My dataset is structured as follows (only relevant columns shown):

Screen Shot 2019-10-16 at 12.53.25 pm.png

"Operational Places" is grouped by "ACPR Name" and "Organisation Type". "Number of Clients" is grouped only by "ACPR Name" in a different ETL, hence why the numbers are the same for each ACPR throughout.

The idea is to have something like below:

Screen Shot 2019-10-16 at 1.38.03 pm.png

I've implemented the solution from a similar question that was asked however ran into a different problem with colour rules/legend labels. If I add colour rules for the new Beast Modes that I create I end up with a messy legend:

Screen Shot 2019-10-16 at 1.40.46 pm.png

Appreciate any help or direction

Cheers

Eric

Find more posts tagged with

Comments

Ashleigh

@eric_tetik you should be able to use the stacked bar and line chart, just make sure you put the column you want to make the line in the y-axis spot and put the others in the series spot.

eric_tetik

Thanks for your response @Ashleigh

I've already tried that and unfortunately it doesn't work. It does works in this simple form:

Screen Shot 2019-10-17 at 9.05.09 am.png However I can't separate the bars into series of "Organisation Type" like the image in the original post.

Screen Shot 2019-10-17 at 9.05.55 am.png

At best, I just receive various versions of the above. The user in this question ran into the same issue in 2017.

Cheers

Eric

Ashleigh

@eric_tetik are you sorting by any columns?

eric_tetik

Hi @Ashleigh

I've tried with and without sorting and neither get me closer to the intended effect. Decided to present the cards differently so no longer an issue, however if you (or anyone) have a solution it'll be great for future reference and other users

Cheers

Eric

MichelleH

Hi Eric, you could create a separate beast mode field for the Operational Places of each Organisation Type. For example:

sum(case when `Organisation Type` = 'Private' then `Operational Places` else 0 end)

sum(case when `Organisation Type` = 'Not-for-profit' then `Operational Places` else 0 end)

sum(case when `Organisation Type` = 'Government' then `Operational Places` else 0 end)

Once you have these Beast Modes, place each of them in the Series section of the Analyzer and put the "Number of Clients" field in the Y Axis postition.

Crisocir

Are there any other solutions or work arounds. In my situation I have hundreds+ "types" that need to be stacked. So its not really an effective use of my time to create a BM for each type. The way DOMO has build the use of this chart is not intuitive at all.

jaeW_at_Onyx

it might benefit you to start a new thread b/c it's unclear if your problems are actually related to the initial issue.

either way.

definitely do not make 100s of beast modes! that would be such a PITA to maintain.

if you have multiple "Types" in one column, experiment with putting Types in "Series" or "Categories". Also, consider a different visualization. if you actually have hundreds of types then it won't make sense to see each individual one in a chart.

If you need to group them so you get down to 5 or 10 unique values, consider use a Beast Mode / CASE statement to group different Types.

Alternatively if you only have a few types of interest, you can alter your data to put the metrics in columns. but personally I think the model you currently have sounds better / more flexible.

jaeW_at_Onyx

to the original poster / @MichelleH and @Ashleigh , the design of the dataset was flawed which is why the user struggled to solve their problem.

the big walk away is to try to avoid having columns that are non-additive or semi-additive. (a fully additive column means you could take the whole column, sum it up and get 'the right answer'. in this case, b'c 'number of clients' was duplicated across rows, the column is only semi-additive. (it's the same as if you had a dataset that had one row per month and the balance of the AR account. the balance column is not additive, b/c you cannot sum all balances across all months and get the right answer. in that kind of data structure you'd always want 'the most recent balance.' and that's way more difficult to do in ANY visualization platform.

SOLUTION: split the metrics into a set of rows for "number of clients" and a set of rows for "count of location"

from there it's just a matter of putting ACPR on the X-axis, Organization Type on the Y-axis. and then your metrics on the series.