Pivot Table Issues

[#1]

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

[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.

[#3]

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

[#4]

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.

[#5]

Hi Joe

Thanks for the suggestion re: calculated dimension.

I've used a calculated dimension - =if(v_ChartDateType=2,WeekCommencing,Month)

Unfortunately I don't think this will work I'm getting "Allocated Memory Exceeded"

Thanks

[#6 · Accepted solution]

Create a new variable

vFieldname

'[' & if($(v_ChartDateType)=2,'WeekCommencing','Month') & ']'

Then in your dimension use

=$(=$(vFieldname))

Should work fine for you

Edit: added example

hope that helps

Joe

[#7]

Thanks Joe

Just out of interest - why is there a performance benefit when using a variable rather than typing out that expression as a calculated dimension?

[#8]

Welcome, glad to help.

There isn't, you were getting a problem as the expression you were using wasn't $ expanding around the variable.

As to why a variable or not, I like to use the variable as it looks tidier and when I do things like this, the variables are created dynamically within the script, so changes are easier to make without missing a chart etc

Joe