Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
@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
))
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]))
))
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
@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
))
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]))
))