topic drilldown - cumulative percentage calculation in QlikView

drilldown - cumulative percentage calculation

Anonymous — Tue, 19 May 2015 15:39:22 GMT

Hi All,

I have two drilldown groups, Group1 and Group2 and one expression.

I would like to display a line chart.

In the chart, when I drilldown from Dimension1 to Dimension2,

The percentages at row level should add upto the percentage at column total. At every dimension level
the percentages achieved at dimension2 level should add upto the total percentage achieved at dimension1 level.
the percentages achieved at dimension3 level should add upto the total percentage achieved at dimension2 level.

For eg., if the percentage achieved at Dimension1 level for a field is 40%, then, all the row values at dimension 2 level

Should add upto 40% only

Re: drilldown - cumulative percentage calculation

ramoncova06 — Tue, 19 May 2015 16:15:44 GMT

so do you have 2 or 3 dimensions ?

here is an example that something you can work with, the assumption is that you want to only keep one expression and that it is modified based on the group selections

Re: drilldown - cumulative percentage calculation

Anonymous — Tue, 19 May 2015 17:47:28 GMT

HI Ramon,

I have two drilldown dimension groups.

Group1 has 3 dimensions

Group2 has 3 dimensions.

When I tried to replicate the logic from your earlier qvw attachment, I am ending up with same percentage for all values by Dimension in 1st group. Please advise

Re: drilldown - cumulative percentage calculation

ramoncova06 — Tue, 19 May 2015 18:44:39 GMT

the trick is on the "total" and the grouping by

can you share the expression you created ?

Re: drilldown - cumulative percentage calculation

Anonymous — Tue, 19 May 2015 21:30:32 GMT

Please see the attached Expression.

also attached is a screenshot

Re: drilldown - cumulative percentage calculation

ramoncova06 — Wed, 20 May 2015 01:51:36 GMT

everything does seems to be ok, not sure what the problem could be, let me see if I figure out another way of pulling this off

Re: drilldown - cumulative percentage calculation

Anonymous — Wed, 20 May 2015 17:52:28 GMT

Hi Ramon,

just to point out, I have two drilldown groups ..

I am included additional dimension and a group to your qvw sample attachment. Please see if it gives any ideas

Re: drilldown - cumulative percentage calculation

ramoncova06 — Wed, 20 May 2015 19:24:13 GMT

I did overlooked on a second group being used ... though here is another option

Just for you take into consideration that because of the fact of having a second dimension your total can be skewed or show different numbers from what you are expecting, for example in the one below, for "x" your total is a sum of all the values that contain x and because for the combination of x and x5 there is more than one value, once you drill into a lower level the numbers are going to look different

Re: drilldown - cumulative percentage calculation

Anonymous — Thu, 21 May 2015 23:10:29 GMT

Hi Ramon,

I so thought I had it.....

I tried to replicate your example. However, I am missing out on some logic here and couldn't get my head around it.

Can you please check out the attached qvw document

Re: drilldown - cumulative percentage calculation

ramoncova06 — Fri, 22 May 2015 02:18:42 GMT

so the issue is only with %delay now ?

Re: drilldown - cumulative percentage calculation

ramoncova06 — Fri, 22 May 2015 02:59:10 GMT

this one works according to the description in the document

Re: drilldown - cumulative percentage calculation

Anonymous — Fri, 22 May 2015 14:15:46 GMT

Hi Ramon,

It is only working by first drilldown group, not by second drilldown group.

I am pulling my hair out 😕

Please see the attachement.

Re: drilldown - cumulative percentage calculation

ramoncova06 — Tue, 26 May 2015 20:25:02 GMT

try with a nested if

=if(GetCurrentField(DrilldownByTime) = 'TripMonthYear',

if (GetCurrentField(DrilldownByDelay) = 'Rail', Num(sum(LateTrips)/count(LateTrips),'##.##%'),

Num(sum(LateTrips)/COUNT( total <TripMonthYear> {<DelayType>} LateTrips),'##.##%')

), Num(sum(LateTrips)/COUNT( total <TripMonthYear> {<DelayType>} LateTrips),'##.##%')

)