Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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

View solution in original post