6 Replies Latest reply: May 11, 2017 4:19 AM by Sunny Talwar RSS

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

    Bart Schockaert

      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