Qlikview: Using a variable or getFieldSelections in Set Analysis not working instead of hardcoding

[ChristopherBirnbaum]

Hi, i have witnessed a strange behaviour with set-Analysis Expressions:

working (Green, "Produktion Set Analysis"):

Sum (
 {$<d_ultimo_datum_neuabschluss_statistik.jahr_monat_nummer = //begin set analysis
 {//begin list of values
 '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06' //debug, hardcoded list
 } //end list of values
 > } //end set analysis
 neuabschluss_anzahl)

not working (Red, "Produktion Set Analysis VAR" using a variable instead of hardcoded list):

Sum (
 {$<d_ultimo_datum_neuabschluss_statistik.jahr_monat_nummer= //begin set analysis
 { //begin list of values
$(var_Produktionsjahre)
 } //end list of values
 > } //end set analysis
 neuabschluss_anzahl)

variable defintion for "var_Produktionsjahre":

Chr(39) & //beginning single quote
Replace( //add single quotations
GetFieldSelections(d_beginn_datum.jahr_monat_nummer,',',12) //max 12 entries for entire year
, ',', Chr(39) & ',' & Chr(39)) //close replace
& Chr(39) //closing single quote 

I want to have a user's selection applied to determine the sum in based on Field "neuabschluss_anzahl"

For testing i've always selected more than one Month, column "Produktion" shows the manually selected data which i want to achieve with a dynamic set-analysis

The selected fieldvalues and variable output are also shown as expressions in the attached screenshot on the very right 😞

I've found a different solution in the meantime to achieve my goal, still i want to gather feedback on what i did wrong when using the variable or GetFieldSelections function

I've browsed through some older posts, but didn't find anything useful on why replacing hardcoded values with a function output does not work. When i try to use double quotes the expression will just return 0

btw: I'm running QlikView May 2022 SR2

BR, 

Christopher

[Mark_Little]

Hi, Can you share what the output of the variable is? I.e. add it to a text field to see matches what you expect?

For it to work it will need to Show as you first working example.

'2017-01','2017-02','2017-03','2017-04','2017-05','2017-06'

If you are using get field selection you will probably find the syntax return from the variable is incorrect

[ChristopherBirnbaum]

hi, variable output is shown in the screenshot, last column. it is exactly as expected:

'2017-01','2017-02','2017-03','2017-04','2017-05','2017-06'

[marcus_sommer · Accepted solution]

In general should your approach be working. If not it means that the created variable isn't identically with the hard-coded stuff (which is sometimes very hard to detect) and/or the variable isn't appropriate called and/or the variable contains commas which will be treated as parameter-delimiter and not as part of the variable-content.

So you may try to change the variable-call from:

$(var_Produktionsjahre)
to 
var_Produktionsjahre

to prevent the the $-sign expansion or you could try to change the variable-creation to a result of:

("2017-01"|"2017-02"|"2017-03"|"2017-04"|"2017-05"|"2017-06")

whereby avoiding the use of strings like 2017-01 and using instead a numeric value of 201701 would simplify such logic significantly because no quoting of the entries would be needed anymore.

Further you need to be careful with comments within variables. Valide created variables with LET (script) or with the starting = (UI) could contain comments because the content-evaluation happens before the the value-assigning but by the SET creation the comments would become a part of the variables which caused often quite difficult to detecting issues. 

Beside of this it's very seldom helpful to use getfieldselections() within a set analysis - much simpler would be to use the selection-functions p() and e(), like:

Sum({$<d_ultimo_datum_neuabschluss_statistik.jahr_monat_nummer=
       p(d_beginn_datum.jahr_monat_nummer)>} neuabschluss_anzahl)

[vincent_ardiet_]

Marcus solution is the best way.
However, if you want to stick with your variable, could you try to add "=" at the beginning of var_Produktionsjahre definition?

[ChristopherBirnbaum]

thank you very much for the explanation and proposed alternative using the "p" function in set analysis - works like a charm!