Cannot get set analysis with variable selection criterion to work; what would be the correct syntax?
Bart Schockaert May 10, 2017 9:18 AMI'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 (midfunction 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 setanalysis 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 bidimension conditions in a pivot table ')
Thanks for any useful feedback,
Bart

TestRep2.qvw 165.2 K