topic Fixed/locked dimensions in a pivot in QlikView

Fixed/locked dimensions in a pivot

RSvebeck — Mon, 20 May 2013 08:38:43 GMT

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

Re: Fixed/locked dimensions in a pivot

Gysbert_Wassenaar — Mon, 20 May 2013 08:43:52 GMT

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

Re: Fixed/locked dimensions in a pivot

sushil353 — Mon, 20 May 2013 08:46:35 GMT

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

Re: Fixed/locked dimensions in a pivot

RSvebeck — Mon, 20 May 2013 08:51:10 GMT

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

Re: Fixed/locked dimensions in a pivot

RSvebeck — Mon, 20 May 2013 08:53:58 GMT

Hi Sushil,

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

Regards,

Robert

Re: Fixed/locked dimensions in a pivot

Gysbert_Wassenaar — Mon, 20 May 2013 09:01:36 GMT

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

Re: Fixed/locked dimensions in a pivot

RSvebeck — Mon, 20 May 2013 09:10:12 GMT

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

Re: Fixed/locked dimensions in a pivot

RSvebeck — Mon, 20 May 2013 10:18:22 GMT

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

Re: Fixed/locked dimensions in a pivot

Mon, 06 Jan 2014 11:31:29 GMT

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

Re: Fixed/locked dimensions in a pivot

Mon, 06 Jan 2014 12:33:14 GMT

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

Thanks,