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

    drilldown Aggr in multiple dimension pivot table - how

    M Paeper

      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
          M Paeper

          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
              M Paeper

              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