topic Re: How get the list of fields NOT GetCurrentSelection in QlikView

How get the list of fields NOT GetCurrentSelection

arunqlik2view — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Suppose if I have column called Region

Region	Total Value
North	200
South	400
West	500
East	100

If I choose North , then in a text box I should get calculation as (sum(South+West+East) - North)
(i.e) (sum(400+500+100)- 200)

and the answer should be 800.

Please advise.

Regards,

Arun

Re: How get the list of fields NOT GetCurrentSelection

qlikviewalex — Fri, 25 May 2018 09:15:10 GMT

Hi Arun,

try this

=sum({1-$}[Total Value])-sum([Total Value])

Re: How get the list of fields NOT GetCurrentSelection

arunqlik2view — Fri, 25 May 2018 09:39:35 GMT

Hi ,

Thank you for the quick post. Actually I tried slightly different syntax and that seems to work

Sum({1} [Value]) -Sum({$} [Value])

- this syntax is sum of all region values - sum of current region . I used this resource file:///C:/Users/hp/Downloads/Les%20set%20analysis_ENG%20(1).pdf.

Thanks again for your quick reply .

Regards,

Arun

Re: How get the list of fields NOT GetCurrentSelection

Anil_Babu_Samineni — Fri, 25 May 2018 09:43:07 GMT

Can this work?

=Sum({<Region=e(Region)>} [Total Value])-Sum([Total Value])

Re: How get the list of fields NOT GetCurrentSelection

arunqlik2view — Fri, 25 May 2018 10:22:26 GMT

Hi Alexander,

Could please explain , what does your syntax do. I tried and it works but I couldn't understand the output. Please explain if you can .

Regards,

Arun

Re: How get the list of fields NOT GetCurrentSelection

stevedark — Fri, 25 May 2018 11:50:41 GMT

I would do the sum ignoring the selection on region, and then take the selected region off twice (once as it is not included and once to remove it) so;

sum({<Region=>}Value) - (sum(Value)*2)

Obviously if there is no region selected it will give you the total value as a negative (1200-2400), but you could put something around it to only show if sum({<Region=>}Value) <> sum(Value)

Using {1} in any expression will ignore all selections.

Hope that helps.

Steve

Re: How get the list of fields NOT GetCurrentSelection

shiveshsingh — Fri, 25 May 2018 12:19:47 GMT

Try this

=sum({<Region=-{"$(=GetFieldSelections(Region))"}>} [Total Value])

-sum({<Region={"$(=GetFieldSelections(Region))"}>}[Total Value])

Re: How get the list of fields NOT GetCurrentSelection

Anonymous — Mon, 28 May 2018 07:37:34 GMT

Hi Arun,

Try below code in text object,

=(Sum({<Region={'*'}>} value)-Sum({<Region={"$(=GetFieldSelections(Region))"}>} value))-Sum({<Region={"$(=GetFieldSelections(Region))"}>} value)

hope it will work.

Re: How get the list of fields NOT GetCurrentSelection

arunqlik2view — Mon, 28 May 2018 08:31:31 GMT

If I choose North , then in a text box I should get calculation as (sum(South+West+East) - North)
(i.e) (sum(400+500+100)- 200)

and the answer should be 1000. ( Sorry , I mentioned as 800 in my previous post . )

Re: How get the list of fields NOT GetCurrentSelection

Anonymous — Mon, 28 May 2018 08:45:46 GMT

Hi Arun,

How can you get (sum(400+500+100)- 200) =1000

But then to if you want the value which excludes selected Region then use

=(Sum({<Region={'*'}>} value)-Sum({<Region={"$(=GetFieldSelections(Region))"}>} value))

IE. sum(South+West+East)

sum(400+500+100)=1000

Re: How get the list of fields NOT GetCurrentSelection

shiveshsingh — Mon, 28 May 2018 09:48:29 GMT

How come (sum(400+500+100)- 200) = 1000?

Re: How get the list of fields NOT GetCurrentSelection

stevedark — Mon, 28 May 2018 21:07:47 GMT

So you want the value excluding the selected, rather than the value with the selected value removed?

The code I posted above (and the reasoning) is all correct, just don't double count the selected value:

sum({<Region=>}Value) - (sum(Value))

That gives you the total ignoring any region selections, less the total of the selected.

Steve

Re: How get the list of fields NOT GetCurrentSelection

arunqlik2view — Tue, 29 May 2018 05:04:26 GMT

It's actually sum of all regions - anyone current selection of region , in this example I chose 'North' . So the equation would be (Sum(400+500+100+200) - 200) = 1000.

I was in the hurry. My apologies for the confusion. Also, I got it worked by using this formula

Sum({1} [Value]) -Sum({$} [Value]).

Re: How get the list of fields NOT GetCurrentSelection

shiveshsingh — Tue, 29 May 2018 05:17:44 GMT

Please close the thread

Re: How get the list of fields NOT GetCurrentSelection

tresB — Tue, 29 May 2018 05:27:54 GMT

The same could be simplified like:

Sum({1-$} [Value])

Re: How get the list of fields NOT GetCurrentSelection

stevedark — Tue, 29 May 2018 07:50:57 GMT

Does this always give the value you want? Your question asked that if you selected a region the sum of the other regions is shown. The code you have there will also include values excluded for other reasons - for instance if you select month.