Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Pivot Table Issues

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

View solution in original post

7 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Pivot Table Issues

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.


talk is cheap, supply exceeds demand
Highlighted
Not applicable

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

Highlighted
Not applicable

Re: Pivot Table Issues

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.

Highlighted
Not applicable

Re: Pivot Table Issues

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

Highlighted
Not applicable

Re: Pivot Table Issues

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

View solution in original post

Highlighted
Not applicable

Re: Pivot Table Issues

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?

Highlighted
Not applicable

Re: Pivot Table Issues

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