4 Replies Latest reply: Jun 6, 2011 9:34 AM by Tom Cotterill RSS

    Pivot table groups, totals and set analysis.

    Tom Cotterill

      Greetings,

       

      I am having a problem with a Pivot table and groupings using set analysis. I have attached a file that demonstrated the problem and any help will be appreciated.

       

      You can see from the file I have 3 dimensions in a pivot table:

      • Year,
      • PGroup (Parent group),
      • SGroup (Sub group)

       

      I am totalling the counts of the 'Type' field by Year and SGroup. To do further analysis of the data I also need the overall total for the PGroup, by year, for each SGroup.

       

      To keep things looking neat I want to select only one SGroup. For instance, if I select 'a1' I would want to see the row for the other SGroups (a2, a3) disappear but leave the overall yearly total for the PGroup intact.

       

      However what is happening is that the rows for a2 & a3 remain with a 0 for the SGroup total and the PGroup total populated on every line.

       

      Current (with no SGroup selection):

       

                                                                  2010                          2011

      -------------------------------------------------------------------

      SGroup      Total SGroup      Year        Total SGroup      Year

      -------------------------------------------------------------------

      a1          2                 3           1                 4

      a2          1                 3           2                 4

      a3          0                 3           1                 4

       

      Desired (after selecting a1):

       

                                                                  2010                          2011

      -------------------------------------------------------------------

      SGroup      Total SGroup      Year        Total SGroup      Year

      -------------------------------------------------------------------

      a1          2                 3           1                 4

       

       

      Is there any way to achieve this?

       

      Many thanks!