Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Not applicable
Author

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

FactoryReport.png

HTH

View solution in original post

2 Replies
Not applicable
Author

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.

Not applicable
Author

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

FactoryReport.png

HTH