2 Replies Latest reply: Aug 19, 2014 3:37 PM by Jonathan Poole RSS

    Pivot Tables

      Can anyone tell me how to achieve this?

       

      I have a straight chart like this right now. 'Week' and 'Product' are dimensions while 'City'  'Type' and 'Orders' are expressions using SUM(field).

      WeekProductStateCityTypeOrders
      Week 11ACaliforniaFremontPlastic40
      Week 11ACaliforniaFremontPlastic50
      Week 21ACaliforniaFremontPaper

      50

      Week 22BArizonaPhoenixPaper100
      Week 22BArizonaPhoenixPaper60
      Total300

       

       

      Is there any way to show the the following without all the other information?  Or would I need to create some other 'Totals' Table?

      Week 1 Total for all plastic orders

      WeekProductStateCityTypeOrders
      Week 1Plastic90

       

      or

      Week 1 from Fremont

      WeekProductStateCityTypeOrders
      Week 1Fremont140
        • Re: Pivot Tables
          Saradhi Balla

          in your expected output as you show in your post, do you need to show the columns even though they have no info?

           

          why not use list boxes for the columns that you don't need to show in your table and use the dimensions you need to show? so in your example, why not make have City and Type (and possibly Product and State as well) as list boxes and remove them from your straight table? if you want to then make it a pivot table, all you have to do is go to properties, go to the general tab and click on pivot table in the chart type and click ok.

           

          ps: in your example, week 1 from Fremont table should be 90 and not 140.

          • Re: Pivot Tables
            Jonathan Poole

            You could try a pivot table chart with Week as the 1st dimension.  For the 2nd dimension i suggest you create a new group by hitting the 'groups' button on the dimension tab. Add Type and City to the group and ensure its a cycle group. Add the new group as a 2nd dimension . 

             

            On the presentation tab, enable 'partial sums' on the group dimension.

             

            it will give you subtotals and allow users to dynamically switch which field you are subtotalling.