Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
RSvebeck
Specialist
Specialist

Fixed/locked dimensions in a pivot

Hi

I have a pivot table with three dimensions.

As expected, when I make selections in my document, the dimensions are reflecting the possible values based on what data I actually have, like always in Qlikview. The more selections i make, the smaller my pivot gets (showing fewer and fewer values in my dimension columns).

My issue is that I now need to make the pivot appearance fixed.

I want the pivot to show all values in my dimensions regardless of what selections I make in the dokument.

I do not want to make it  detached, since the numbers in my pivot must change depending on what I select, but the numbers should be zero or null if there are no valid numbers.

I have tried "Show all values" but it still does not make my pivot dimensions fixed.

Is it possible to do this?

brg

Robert

Svebeck Consulting AB
1 Solution

Accepted Solutions
RSvebeck
Specialist
Specialist
Author

Hi again

I found a way to solve my issue.

What I do is that I add the general set expression to my expression like this:

sum( {1} 0 ) + count( myKey )

This way all dimension will always remain complete because the sum({1}0) will always return a Zero, regardless of what I select.

Thanks guys for all the help!

Robert

Svebeck Consulting AB

View solution in original post

9 Replies
Gysbert_Wassenaar

If you have QV11 you can create alternate states on the General tab of the document properties window. You can then assign an alternate state to the pivot table on the General tab of the properties window of the pivot table. Only selections made in the same alternate state as that of the pivot table will be applied to the pivot table.

If you don't have QV11 you can use set analysis expressions. For example if you have an expression sum(sales) you could change that to sum({1} sales) to ignore any selections. Or to sum({<Year=>} sales) to ignore selections in the Year field (supposing you have a field with that name).


talk is cheap, supply exceeds demand
sushil353
Master II
Master II

modify your expression as

suppose you are having 2 dimensions say D1 and D2

now in your expression put set analysis as e.g:  sum({<D1=,D2=>}Sales)

doing this if you do any selection in your list box having D1 and D2 it will not reflect the selections.

HTH

Sushil

RSvebeck
Specialist
Specialist
Author

Thank you Gysbert for your reply.

Sorry for beeing unclear on my request.

Using SET or Alternate States will not help me here since I do want the selections to effect my pivot expression - its just that I always want to keep all my dimensions displayed regardless of my selections.

I have placed clickable text objects on top of my pivot and for this reason I need the layout of the pivot to remain the same size, but still showing the correct values based on the selections thatis made.

//Robert

Svebeck Consulting AB
RSvebeck
Specialist
Specialist
Author

Hi Sushil,

Thanks for your reply. Please read my reply to Gysbert. My problem is still unsolved.

Regards,

Robert

Svebeck Consulting AB
Gysbert_Wassenaar

Add an expression sum({1}1) and hide the expression


talk is cheap, supply exceeds demand
RSvebeck
Specialist
Specialist
Author

Hi again.

Its a very good idea and I will use it if I find no other solution! But since this is a pivot I can not hide the expression. I can make it blank and thin, but there is no way to hide it.

Best Regards

Robert

Svebeck Consulting AB
RSvebeck
Specialist
Specialist
Author

Hi again

I found a way to solve my issue.

What I do is that I add the general set expression to my expression like this:

sum( {1} 0 ) + count( myKey )

This way all dimension will always remain complete because the sum({1}0) will always return a Zero, regardless of what I select.

Thanks guys for all the help!

Robert

Svebeck Consulting AB
Not applicable

Hello Robert

I have similar requirement where I need pivot table dimension to be locked and only expression changes.  I am trying to use sum( {1} 0 ) + count( myKey ) but it is not working.  I added new  expression Sum({1}0) but with no luck.  Can you please elobrate how you used this?

Regards,

Asha

Not applicable

I go the answer to my question.  I removed the tick from 'Supress Zero values' and it workes fine now.

Thanks,