topic Re: % of Total that does not change with selections in QlikView

% of Total that does not change with selections

MalcolmCICWF — Thu, 05 May 2016 13:47:01 GMT

I have wondered this a few times... what is the easiest way to display the percentage of a total statically so that when I make a selection, it still reflects the percentage of the total, and not the percentage of the volume I have selected?

Pre Selection:

Post Selection:

Re: % of Total that does not change with selections

florentina_doga — Thu, 05 May 2016 13:49:39 GMT

use this

sum(amount)

sum(total amount)

in percent

Re: % of Total that does not change with selections

sunny_talwar — Thu, 05 May 2016 13:49:54 GMT

What is the expression you are using? Assuming it is Count(Account) with relative option checked, you can change it to this:

Count(Account)/Count({1} TOTAL Account) -> With relative unchecked now

or if you want to only let the denominator not change on certain dimensions, you can do like this:

Count(Account)/Count({<Year>} TOTAL Account) -> With relative unchecked now

UPDATE: Forgot to add TOTAL which is not added

Re: % of Total that does not change with selections

MalcolmCICWF — Thu, 05 May 2016 14:37:52 GMT

I'm using that, not working

Re: % of Total that does not change with selections

MalcolmCICWF — Thu, 05 May 2016 14:43:55 GMT

This is what I am using and no matter what, when I select criteria, it changes to % of what is selected.

sum(ID_COUNT)/Sum(Total ID_COUNT)

Re: % of Total that does not change with selections

sunny_talwar — Thu, 05 May 2016 14:45:01 GMT

Try this:

Sum(ID_COUNT)/Sum(TOTAL {1} ID_COUNT)

Re: % of Total that does not change with selections

MalcolmCICWF — Thu, 05 May 2016 15:55:44 GMT

You are like my Qlik guardian angel lately Sunny, which I appreciate. Are you able to explain the use of the {1} within this expression?

Re: % of Total that does not change with selections

sunny_talwar — Thu, 05 May 2016 16:01:24 GMT

Sure thing.

So the reason your % age was changing was because as soon as your made a selection your denominator was also getting filtered based on the selection. Since you don't want the denominator to change, you just need to add {1} to tell it to avoid any selections.

Now, if you only want certain selections to be avoided (while still getting filtered on other selections) you need to specify the names of those fields which should not be filtered

{<Field1, Field2, Field4>}

on the other hand if you have a long list of fields on which it should not filter a few fields on which it should filter, you can do like this:

{1<Field3 = $::Field3>}

Re: % of Total that does not change with selections

MalcolmCICWF — Thu, 05 May 2016 18:29:10 GMT

thanks, very informative

Re: % of Total that does not change with selections

sunny_talwar — Thu, 05 May 2016 18:33:28 GMT

No problem at all