Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alexandrajaber
Contributor
Contributor

100% Stacked bar chart issue (stack showing anything but 100% )

Hi All

 

I have been trying to create a 100% stacked bar chart with the below scenario:

We are trying to determine the % of each Main Reason for people leaving (6 possible response categories) / sum of all main reasons. We have used 2 group dimensions: 

1. Includes: age, band, gender...

2. Includes: Main reason and sub reason

 

Each leaver can select multiple reasons for leaving.

 

We have tried the following measure:

Measure 1= Sum(distinct {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}>} Leavers_Count)

Measure 2=count(distinct {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}>} EmployeeID)

Measure 3 =Sum(distinct {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}>} EmployeeID)

Measure 4= Sum( {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}>} Leavers_Count)
/Sum( Total <[Main Reason]> {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}>} Leavers_Count)

 

I have attached screenshots of the graph for each of the 4 measures (measure 1 and 2 show the same). Unfortunately, I am not able to attached the app or a sample app due to security reasons.

 

The results of the 4th measure makes the most sense but it adds up to 300%. 

 

Any suggestions or tips?

 

Thanks!

3 Replies
Simon_Astakhov
Partner - Contributor III
Partner - Contributor III

Measure 4 is close enough. 😃

Your chart have 2 dimensions, but 100% u need by each.

Try Count( [reason] ) / Count( TOTAL <gender> [reason] )

It will return Count( [reason] ) for each reason type (six of them) and gender. And Total will return total values for each gender.

alexandrajaber
Contributor
Contributor
Author

This kind of works whereby it gave me 100%, the issue now is that for each main reason we can have several sub reasons so we need to be able to count(distinct(EmployeeID)) somewhere because if 1 main reason has multiple sub-reasons, the % shown will not be accurate and the only way to deduplicate is if we count it based on EmployeeID.

How do you recommend we introduce that to the following expression: 

Count( {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}, [Exit Interview Eligible]={'True'}>} [Main Reason] ) /
Count( TOTAL <Gender, [Global Band (E)],Tenure, [Age Grouping], Country, [Company Code],Discover,Columbus,Nationality> {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}, [Exit Interview Eligible]={'True'}>}[Main Reason] )

 

?

 

Thanks!

alexandrajaber
Contributor
Contributor
Author

I tried the following expression but the distinct part is bringing the stacked bar to where it doesn't show 100% anymore... when distinct is removed, the stack goes back to 100% and the numbers are pretty close to what they should be but not exact..

 

Count(DISTINCT {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}, [Exit Interview Eligible]={'True'}>} [EmployeeID] ) /
Count ( distinct TOTAL <Gender, [Global Band (E)],Tenure, [Age Grouping], Country, [Company Code],Discover,Columbus,Nationality> {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}, [Exit Interview Eligible]={'True'}>} [EmployeeID] )