Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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 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