topic Re: 100% stacked bar chart in QlikView

100% stacked bar chart

brc111086 — Mon, 19 Nov 2018 20:01:02 GMT

I created a stacked bar chart where the Y-axis is Manager Level and the X-Axis is Headcount #s. The other dimension besides Manager Level is Gender. Each bar shows a total headcount # for each of our Manager Levels, and each bar is broken up by Male and Female.

Currently, the #s displayed represent actual headcount #s. I am trying to change the "#s" to a % of Manager Level total. For instance, on our VP Manager Level I would like to see the % of Males vs. the % of Females within that group. All stacks would total 100%.

Could someone please let me know what the best expression would be for what I am looking or how best to get this?

Thanks in advance for your help.

Re: 100% stacked bar chart

Nicole-Smith — Mon, 19 Nov 2018 20:53:19 GMT

If your calculation to get the head count looks something like this (with sum/count/etc.):

count(FieldName)

Then you would get the percentage like this:

count(FieldName)/count(total FieldName)

Then you'll need to add your other fields into <> in order to calculate the percentage for each bar instead of as a total:

count(FieldName)/count(total <ManagerLevel, Gender> FieldName)

Re: 100% stacked bar chart

brc111086 — Mon, 19 Nov 2018 21:17:14 GMT

Nicole-

Thanks for the response back. I tried the formula you recommended, but it did not seem to work.

My Manager Levels are as follows:

Other Personnel
Non-Management
Assistant Manager
Supervisor
Manager
Director
VP's
Dept Chair/SrOffDirectReport
Senior Officer
President

Gender is just

Should I only be using this expression you provided: count(FieldName)/count(total <ManagerLevel, Gender> FieldName)? And do I enter every Manager Level separated by commas. I have only been working with Qlik for a few weeks and not too advanced on putting the expressions together so I appreciate your assistance.

Re: 100% stacked bar chart

lblumenfeld — Tue, 20 Nov 2018 06:48:35 GMT

My understanding is that you want to have on bar for each level, have the total be 100%, and have the bar be split by the percent of each gender for that specific level. Correct?

If so then this is what you do.
My example uses fields named Level, and Gender. Change them to your field names.

1. Create a bar chart.
2. Select Level for the first dimension.
3. Select Gender for the second dimension.
4. Use this formula for the measure:

Count(Gender)/Count(total <Level> Level)

Note: if you have an ID field (I called mine ID) for each person then you can also use:
Count(Gender)/Count(total <Level> ID)

Either formula will work.

5. Change the format pattern for the measure. Turn off auto and use 0.0%
6. Change the label for the measure to "Gender %".
7. In the Appearance section, change the presentation to "Stacked".
8. Add your title.

Your chart should look something like the example attachment. I only used M and F for Gender, so mine will look a little different.

I hope this helps.

Re: 100% stacked bar chart

brc111086 — Tue, 20 Nov 2018 13:45:14 GMT

The last suggestion worked perfectly! Thanks so much!

Re: 100% stacked bar chart

lblumenfeld — Tue, 20 Nov 2018 15:44:27 GMT

Excellent! Please mark the example as correct, so that others will see it when they search. Thanks.

Re: 100% stacked bar chart

aarav021 — Fri, 27 Oct 2023 07:14:51 GMT

Will this formula work with Avg ? Because in my case the stack in total is not giving 100% in total