Dynamically Using Variables in Set Expression

[adh79]

Hi everyone,

I'm working on a set expression in Qlik Sense and need your help to implement dynamic variables in my code. Here's what I have so far:

Sum(Aggr(

   If(Sum({<NotExistsInNextMonthRN={1}>} 1) > 0, 1, 0),

   ID, Date, RN

))

In my load script, I have defined two variables:

Set vOrgType1 = 'NotExistsInNextMonth' & GetFieldSelections(OrgLevelType); // text string + selected field (eg. RN)

Set vOrgType2 = GetFieldSelections(OrgLevelType); // selected field (eg. RN)

I want to replace the dimension ‘NotExistsInNextMonthRN’ with the variable vOrgType1 and the dimension ‘RN’ with the variable vOrgType2.

I have tried several options, but none of them work, even though I have tested that both variables evaluate to the correct names.

How can I apply the values from vOrgType1 and vOrgType2 correctly in my set expression?

Thank you in advance for your help!

[Qrishna]

1. Inside set expression, afaik You cannot use a variable in the place of the field name unless variable is assigned a field. Eg vOrgType1 = NotExistsInNextMonthRN

2. you should be able to work with vOrgType2 for grouping

[Kushal_Chawda · Accepted solution]

@adh79  If I understood it correctly, you need to define below variable on front end instead load script

vOrgType1 = 'NotExistsInNextMonth' & GetFieldSelections(OrgLevelType)

Then you can use below measure

Sum(Aggr(

   If(Sum({<[$(vOrgType1)]={1}>} 1) > 0, 1, 0),

   ID, Date, RN

))

[adh79 · Accepted solution]

Thanks. I have changed the logic. It now works.

Sum(Aggr(
If(Sum({<[$(='NotExistsInNextMonth' & GetFieldSelections([OrgLevelType]))]={1}>} 1) > 0, 1, 0),
ID, Date, $(=GetFieldSelections([OrgLevelType]))
))