Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cannot get set analysis with variable selection criterion to work; what would be the correct syntax?

I'm trying to make set analysis work with selectin criterion via a variable; while I expected this to function, it does not somehow. What is the correct syntax? I have consulted all documentation on this forum, but could not find a solution. My approach should be able to work, but somehow it does not. Can anybody point out my (apparently) obvious mistake?

Attached is the QV file. Here under some info (is in the file):

Variable definition:

vDim1 --> mid('xx BE NL SG xxx',1+($(vDim1A)-1)*3,2)

vDim1A -->  Rep1_EntCode

Expression formula:

=pick(Testline,
// Testline = 1
Rep1_EntCode,

// Testline = 2
$(vDim1A),

// Testline = 3
mid('xx BE NL SG xxx',1+($(vDim1A)-1)*3,2),

// Testline = 4
$(vDim1),

// Testline = 5
pick(Rep1_EntCode,
  0,
  sum({<Ent_Rep1 = {"BE"} >} [Val_Rep1]),
  sum({<Ent_Rep1 = {'NL'} >} [Val_Rep1]),
  sum({<Ent_Rep1 = {[SG]} >} [Val_Rep1])
  ),

// Testline = 6
// This should work, but does not, even though the value of $(vDim1) equals the correct (string-) entity codes
sum({<Ent_Rep1 = {"$(vDim1)"}> } [Val_Rep1]),

// Testline = 7
// This does not work (invalidates all output)
// sum({<Ent_Rep1 = {$(vDim1)}> } [Val_Rep1]),
'invalid syntax',

// Testline = 8
// Another attempt, including an '=' sign; does not work either
sum({<Ent_Rep1 = {"=$(vDim1)"}> } [Val_Rep1]),

// Testline = 9
-1,

// Testline = 10
'End', ...

Comments:

Lines 1 (table value) & 2 (table value via variable) yield the same result

Lines 3 (mid function result with 1 variable) & 4 (mid-function with variable, via a 2nd variable) yield the same result

Line 5 shows the correct result of set analysis via hardcoded string values

I would expect that line 6 works, because the value between the {} is the same (as proven in line 4)
But it does not work...

I tried some more variants, but to no avail.

Question: how can I get line 6 to function correctly?

I already consulted all available documentatoin of set analysis on this forum, but cannot link this to info on string variables between the {}.
I'm specifically / only looking for a set-analysis solution with a variable expression between the {}; this solution will help solve a major reporting problem - see an earlier question named 'How to get set analysis to work using multiple bi-dimension conditions in a pivot table ')

Thanks for any useful feedback,

Bart

1 Solution

Accepted Solutions
sunny_talwar

Its good to be skeptical, but here is my explanation

Dollar sign expansion within set analysis is done at chart level. What this means is that if you create a text box object with your variable, the value it shows will be used for the whole chart.... so for example if you have a variable like vMaxDate used in set analysis and it shows 01/01/2017, the complete chart will show the information for this particular date.... you cannot program set analysis to show 01/01/2017 for country A and show 01/01/2016 for country B because country B's max is 01/01/2016. What you will see is just country A, because the chart is only going to show 01/01/2017 because of the use of vMaxDate.

Coming back to your example, when I add vDim1 in a text box object, I see '-' which means that there are multiple values within this variable and it will not be of any use in your set analysis... you can use an if statement (which essentially what you have using the pick(match()) statement...

Does this make sense?

Capture.PNG

View solution in original post

6 Replies
sunny_talwar

What you are trying to do won't work because set analysis is evaluated once per chart. You cannot do this dimension wise which is why it doesn't work.

Not applicable
Author

Thanks, Sunny.

I am sceptic about this answer, because I have made - in other pivot tables - set analysis to function correctly with variables containing $-expansions, functions and parameters between the {}, so why would a $-expansion of a variable - which does evaluates correctly; see line 4 - now behave any differently?

What IS different here is that the result of the evaluation between the {} is a string; my previous success evaluated to values. Still not sure why that would make a difference, but one never knows.

sunny_talwar

Its good to be skeptical, but here is my explanation

Dollar sign expansion within set analysis is done at chart level. What this means is that if you create a text box object with your variable, the value it shows will be used for the whole chart.... so for example if you have a variable like vMaxDate used in set analysis and it shows 01/01/2017, the complete chart will show the information for this particular date.... you cannot program set analysis to show 01/01/2017 for country A and show 01/01/2016 for country B because country B's max is 01/01/2016. What you will see is just country A, because the chart is only going to show 01/01/2017 because of the use of vMaxDate.

Coming back to your example, when I add vDim1 in a text box object, I see '-' which means that there are multiple values within this variable and it will not be of any use in your set analysis... you can use an if statement (which essentially what you have using the pick(match()) statement...

Does this make sense?

Capture.PNG

Not applicable
Author

Thanks, Sunny.

I understand what you say about %-sign expansion being done at the chart level, but the evidence in the output contradicts this: see line 2 and 4, where the %-sign expansion does provide the correct values per country; only when used between the {} it (apparently) fails. So for what reason do lines 2 and 4 work?

Assuming the answer to my question above is 'coincidence', the whole point of solving / taking this approach is to avoid an explosion of if's or pick()'s when dealing with a major number of possible values (e.g. countries or more dimensions). Is there another - working - approach that I might take?

Not applicable
Author

Ok, let me answer my own question about why lines 2 & 4 work: it is no set analysis syntax...

Thanks, Sunny.

sunny_talwar

Hahahaha yes, you got it