topic drilldown Aggr in multiple dimension pivot table - how in QlikView

drilldown Aggr in multiple dimension pivot table - how

Wed, 06 Jun 2012 13:44:50 GMT

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

Wed, 06 Jun 2012 15:18:35 GMT

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

Thu, 21 Jun 2012 08:25:59 GMT

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