topic Re: Variable with multiple values used in Set Analysis in QlikView

Variable with multiple values used in Set Analysis

paul_hallwood — Thu, 04 May 2017 11:05:11 GMT

Hello all,

I was wondering if someone can point me in the right direction. Basically I have a variable varGeographicalArea that is defined as follows:

varGeographicalArea = =if(Left(GetFieldSelections([Geographical Area]),3) = 'NOT','NOT',replace(Concat(DISTINCT chr(39)&GetFieldSelections([Geographical Area])&chr(39),','),',',chr(39)&', '&chr(39)))

So this variable can have multiple values contained in it. e.g. 'Place_A', 'Place_B', 'Place C'

Now, when I stick this variable in a formula for a textbox, it only seems to give me the total value for the first value held within the variable varGeographicalArea. This is despite the variable varGeographicalArea holding the value 'Place_A', 'Place_B', 'Place_C'. Here is the formula I'm using for the textbox:

=Sum({1<[Geographical Area] = {$(varGeographicalArea)}>} [Incident_Count])

So, rather than expecting the total for geographical areas 'Place_A', 'Place_B', and 'Place_C', I am only getting a total for geographical Area 'Place_A'.

Any help much appreciated.

Re: Variable with multiple values used in Set Analysis

sunny_talwar — Thu, 04 May 2017 11:10:26 GMT

Try this as your variable

varGeographicalArea = =Chr(39) & GetFieldSelections([Geographical Area], Chr(39) & ',' & Chr(39), 10000) & Chr(39)

Re: Variable with multiple values used in Set Analysis

marcus_sommer — Thu, 04 May 2017 11:13:39 GMT

A variable could contain only one single value at the same time and therefore you couldn't use them within the set analysis on a row-level. But I think in your case the following should be working:

=Sum({1<[Geographical Area] = p([Geographical Area])>} [Incident_Count])

- Marcus

Re: Variable with multiple values used in Set Analysis

paul_hallwood — Thu, 04 May 2017 12:38:06 GMT

I think that's sorted the issue out very nicely! Thank you very much for the speedy response.

Re: Variable with multiple values used in Set Analysis

paul_hallwood — Thu, 04 May 2017 12:44:42 GMT

Hello Marcus - funnily enough your answer was spot on too! Can we mark multiple posts as being the correct answer???

Thank you very much for getting back to me too.

Re: Variable with multiple values used in Set Analysis

sunny_talwar — Thu, 04 May 2017 13:10:55 GMT

I think marcus_sommer‌'s response deserve the correct answer here

Re: Variable with multiple values used in Set Analysis

paul_hallwood — Thu, 04 May 2017 13:17:12 GMT

Done. Thank you for your honesty.