topic Re: Dynamically Using Variables in Set Expression in App Development

Dynamically Using Variables in Set Expression

adh79 — Tue, 05 Nov 2024 11:44:18 GMT

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!

Re: Dynamically Using Variables in Set Expression

Qrishna — Tue, 05 Nov 2024 11:57:46 GMT

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

Re: Dynamically Using Variables in Set Expression

Kushal_Chawda — Tue, 05 Nov 2024 12:02:03 GMT

@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

))

Re: Dynamically Using Variables in Set Expression

adh79 — Wed, 06 Nov 2024 09:02:23 GMT

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]))
))