cancel
Showing results for
Did you mean:
Contributor

## 100% stacked bar chart

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?

1 Solution

Accepted Solutions

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".

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.

6 Replies
MVP

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)

Contributor
Author

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

• M
• F
• U

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.

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".

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.

Contributor
Author

The last suggestion worked perfectly!  Thanks so much!