Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
brc111086
Contributor
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?

Thanks in advance for your help.

 

 

1 Solution

Accepted Solutions
lblumenfeld
Partner Ambassador
Partner Ambassador

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.

 

View solution in original post

6 Replies
Nicole-Smith

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)

brc111086
Contributor
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.

lblumenfeld
Partner Ambassador
Partner Ambassador

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.

 

brc111086
Contributor
Contributor
Author

The last suggestion worked perfectly!  Thanks so much!

lblumenfeld
Partner Ambassador
Partner Ambassador

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

aarav021
Contributor III
Contributor III

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