2 Replies Latest reply: Jun 21, 2012 4:29 AM by M Paeper

# drilldown Aggr in multiple dimension pivot table - how

Hi,

In a load script I have a value calculated as

ClockHrs = totalTime+overTime-absentTime

On QV desktop 11 SR1 I created a pivot table with dimensions of Date, workCenter and Operator and an ClockHrs as an expression.

The Operator dimension is grouped by workCenter (i.e. a workCenter contains many Operators)

Unless one aggregates ClockMins nothing appears in the table as a value for ClockMins.

My issue is that I can only get a ClockMins value to display for one level of drilldown in the pivot table.

e.g. given a pivot table looking like this (the + - below represents the expansion of the pivot table dimension and not a mathematical operator)

Using the ClockMins expression Aggr(ClockMins,Operator) I see this

Date       -workCenter   -Operator   ClockMins

18/05      L01                  1334         660

1335         660

L02                  3992

but if I close the Operator drill down pivot I see this

Date       +workCenter   -Operator   ClockMins

18/05      L01                                      -

If I go the other way round and using the ClockMins expression Aggr(Sum(Aggr(ClockHrs,Operator)),workCenter) I see this

Date       +workCenter   -Operator   ClockMins

18/05      L01                                  1320

if I open the pivot table dimensions I see this:

Date       -workCenter   -Operator   ClockMins

18/05      L01                  1334         -

1335         -

L02                  3992         -

Both results from the Aggr expression are correct but only for one of the drill down dimensions.

How can I get one pivot table column show me the correct Aggr no matter which drill down level I'm viewing.

i.e. at the workCenter view I expect to see 1320 for L01

and at the Operator level view I expect to see 660 shown for each Operator.

Thanks

• ###### drilldown Aggr in multiple dimension pivot table - how

Figured it out after posting.

Sum(Aggr(ClockHrs,Operator))

as the expression gives me the correct answer and aggregates at all pivot table dimension drill down/up levels.

• ###### Re: drilldown Aggr in multiple dimension pivot table - how

Not so fast.

I discovered recently that my discovery wasnt quite that simple - while the above works for a single period when factoring in date dimensions things get more complicated and when doing a Pivot table - you get results that work for a particular date but then when you drag your slider over a date range things start falling apart and you see no or only one aggregation but not for all the dates.

I'll show the formula I presently use which gives me pivot table driill down over multiple dates and all the dimensions I have. Effectively what seems to be required is at the Aggr(expression,dimension) level one adds all the dimensions comma separated in sequence from lowest to highest level of aggregation required.

e.g.

Sum(Aggr((totalTime+overTime-absentTime),Operator,workCenter,CalendarDate,ProdCalWeekNumber,MonthName,YearName))

In practice it looks like this

HTH