12 Replies Latest reply: May 26, 2015 4:25 PM by Ramon Covarrubias

# drilldown - cumulative percentage calculation

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,

1. The percentages at row level should add upto the percentage at column total. At every dimension level
2. the percentages achieved at dimension2 level should add upto the total percentage achieved  at dimension1 level.
3. 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

• ###### Re: drilldown - cumulative percentage calculation

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

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

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

can you share the expression you created ?

• ###### Re: drilldown - cumulative percentage calculation

also attached is a screenshot

• ###### Re: drilldown - cumulative percentage calculation

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

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

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

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

so the issue is only with %delay now ?

• ###### Re: drilldown - cumulative percentage calculation

this one works according to the description in the document

• ###### Re: drilldown - cumulative percentage calculation

Hi Ramon,

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

I am pulling my hair out :/

• ###### Re: drilldown - cumulative percentage calculation

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),'##.##%')

)