7 Replies Latest reply: Dec 17, 2014 7:32 AM by Joseph Simmons RSS

    Pivot Table Issues

      Hi

       

      I'm having an issue displaying pivot table dimensions / expressions in the format I want.

       

      See attachment.

       

      I have 4 dimensions, Sector (always shown), Product (always shown), WeekCommencing (conditional show), Month (conditional show).

       

      I also have 2 expressions - Vol and AOV.

       

      The top table is exactly how I'd like the data to be shown. Sector and Product on the left as my row labels. The date dimension (in this case WeekCommencing) at the top as my column labels. Then the 2 expressions Vol and AOV importantly displayed as rows rather than columns.

       

      Now when I switch my date dimension to Month instead of WeekCommencing, the expressions Vol and AOV move to be displayed as columns instead of rows (bottom table). I can drag the expressions to rows again but when I switch back to the WeekCommencing dimension it will have moved my WeekCommencing dimension to the row labels and the expressions as columns.

       

      What I'd like is for the expressisons Vol and AOV to always be displayed as rows, and the date dimension (Month or WeekCommencing) always displayed as column labels. it sound simple but I can't get this to work.

       

      Any help appreciated.

       

      Thanks

      Adam

        • Re: Pivot Table Issues
          Gysbert Wassenaar

          Perhaps you can use a Cyclic group for the date dimensions instead of conditionally showing and hiding them. Or you could use an expression as dimension using a variable: =$(vMonthOrWeek) and use a button or input box to set the variable vMonthOrWeek to the value Month or the value WeekCommencing.

            • Re: Pivot Table Issues

              Thanks Gysbert

               

              I might try this as a last resort but I need to keep the conditional show buttons to maintain the theme I've applied across the rest of the document.

               

              Ideally I'd like to figure out why this is happening when using conditional shows (if it is the conditional shows that are causing it).

               

              Thanks

              Adam

              • Re: Pivot Table Issues
                Joseph Simmons

                As Gysbert says, a cycle group should work for you, if there is a reason you can't use this and need the conditional dimension.

                The other option would be a calculated dimension which is variable and adjusts depending on your condition but is always showing, rather than 2 separate fields each with their own show condition.