topic Re: Computing percentages in a stacked bar chart in QlikView

Computing percentages in a stacked bar chart

Tue, 27 Aug 2013 19:18:51 GMT

Hi,

I am working on a stacked bar chart. I have two dimensions.

Dimension 1 is a group named Year. It has 3 drill down levels: Fiscal Year, Quarter, and Month

Dimension 2 is a calculated dimension based on the values in a variable:

=aggr(if($(ComputedBench)='25th', $(ComputedBench),

if($(ComputedBench) = '50th', $(ComputedBench),

if($(ComputedBench) = '75th', $(ComputedBench),

if($(ComputedBench) = 'Over 75th', $(ComputedBench))))),

FACNUM, Year)

ComputedBench is a variable that contains 25th, 50th, 75th, Over 75th or Null.

I tried this expression to compute the percentages, but no data was displayed in my chart:

=count($(ComputedBench))/aggr(NODISTINCT Count($(ComputedBench)),Year)

I tried changing it to this expression thinking that counting a value from a variable may be the issue, but the calculation seems to be off since the 4 stacks (based on dimension 2) on my bar chart do not add up to 100%.

=Count(NormTotalExpenseEVS)/aggr(NODISTINCT Count(NormTotalExpenseEVS),Year)

NormTotalExpenseEVS is a field in a table. If a center has NormTotalExpenseEVS filled in, then ComputedBench should definitely have a value other than NULL.

Does anyone have any suggestions about how to make this work?

Thank You!

Re: Computing percentages in a stacked bar chart

Oleg_Troyansky — Tue, 27 Aug 2013 21:43:50 GMT

Try something like this:

=Count(NormTotalExpenseEVS)/Count( TOTAL <Year> NormTotalExpenseEVS)

or, for a dynamic group, try this:

=Count(NormTotalExpenseEVS)/Count( TOTAL <$(=GetCurrentField(MyGroup))> NormTotalExpenseEVS)

when calculating subtotals by chart dimensions, using TOTAL works much better than using AGGR.

cheers,

Oleg Troyansky

www.masterssummit.com

www.naturalsynergies.com

Re: Computing percentages in a stacked bar chart

Wed, 28 Aug 2013 14:56:49 GMT

Thanks for the response. I tried the =Count(NormTotalExpenseEVS)/Count( TOTAL <Year> NormTotalExpenseEVS). My chart showed 100% for all of the dimensions, so I dumped the expressions into a pivot table to look at the pieces of the expression (results are below).

Month	Quartile	TotalExpenseperAPD	Numerator	Denominator
October	Over 75th	100%	31	31
October	75th	100%	46	46
October	50th	100%	54	54
October	25th	100%	65	65

The numerators are correct, but the denominator should be equal to the sum of the numerators for all quartiles combined, which in this case should be 196 for each of these. It looks like it is only considering the quartile in the current dimension when computing the denominators. Any ideas how to get around this?

Re: Computing percentages in a stacked bar chart

Oleg_Troyansky — Wed, 28 Aug 2013 15:52:19 GMT

Shannon,

if you first dimension is Month, then you should use Month as a qualifier for the TOTAL:

Count(NormTotalExpenseEVS)/Count( TOTAL <Month> NormTotalExpenseEVS)

Or, use GetCurrentField() to make the expression work with a dynamic group