Calculating a distribution percentage via a Stacked Bar Chart

[#1]

Hi,

I have been attempting to create something that I thought would have been quite simple.  Essentially what I want to have is have my date (ie - YYYY-MM over 10 years of data so 200501 - 201412) on the X-Axis and calculate the distribution on the sum of open claims by a specific dimension.  For this example, we will use Gender (male / female).

What I want it to do is to calculate the percentage of open claims using either a Stacked Bar Chart or a Funnel Chart showing the percentage split by Male / Female for that specific month.  In other words, if there were 200 open claims in 200501 (150 Male, 50 Female) I would want this to show 75% Male, 25% Female for 200501 --totalling 100%.  Same would occur for 200502 .. 200503 .. etc -- with each specific month totalling 100%.  However, trying to use Aggr, Total, etc does not seem to work.  I keep getting for 200501 something like 2.1% and 3.5%, 200502 I get 2.0%, 3.0% .. etc.. where if you totalled up the percentages across ALL periods you get 100%.

Example Calculations I have tried:

sum(Open Claim Indicator) = the calculation of # of Open Claims and my Numerator

I've tried the following to get my Denominator to no avai...

Sum({<GENDER=>} TOTAL [Open Claim Indicator])

Sum({<GENDER=>} [Open Claim Indicator])

AGGR(SUM([Open Claim Indicator]),DATE)

Any help would be GREATLY appreciated.  I feel like this should be one of the easier things to figure out but I can't seem to have any luck.

Let me know if you would like any more detail behind what I am trying to do.

Thank you in advance for your help!!!

[Oleg_Troyansky · Accepted solution]

This is rather simple if you have a field representing the Months. If you do, then use the following:

1. Stacked Bar Chart or an Area Chart, with MonthYear and Gender as two dimensions.

2. Expression: sum([Open Claim Indicator]) / sum( TOTAL <MonthYear> [Open Claim Indicator])

best,

Oleg Troyansky

Come and learn advanced Aggregation and Advanced Set Analysis with me at the Masters Summit for QlikView!

www.masterssummit.com

[#3]

Thank you Oleg,

I figured it was something real simple like this.  For some reason, I thought if I told my formula to ignore the DATE field it would then calculate the TOTAL number of Open Claims across all dates.  But I plugged this in and it seemed to work.

Thanks again!