6 Replies Latest reply: Jan 26, 2018 2:43 PM by Stefan Wühl

# Variable in set analysis not working

Hi Everyone,

I am trying to get a kpi working using set analysis with a variable in Qlik Sense. The variable is defined as follows:

if(IsNull(Value1) OR IsNull(Value2) OR IsNull(Value3), Null(), Dimension1)

When I use this variable in a table next to value1, value 2 value3 and Dimension1 it works fine. Here is an example

Dimension1vVariableValue1Value2Value3
AAAAAAXXXYYYZZZ
BBB--YYYZZZ
CCC-XXX-ZZZ
DDD-XXXYYY-
EEEEEEXXXYYYZZZ
FFF---ZZZ
GGG-XXX--
HHH----

As you can see, as long as Value1, Value2 or Value3 are not null, Dimension1 matches the variable. However, when I use this in a KPI chart object to count the non-null variables, I get no results. Here is my Set expression:

Count({\$<Dimension1 = \$(vVariable)>} DISTINCT(Dimension1))

Is there a clear reason why this is not working for me? It seems that if the table interprets the variable correctly, the set analysis should too.

I appreciate the help.

Stephen

• ###### Re: Variable in set analysis not working

If your set expression works or not will also depend on your data model and the relation of your fields.

Looks like you are trying to apply an expression search:

Count({\$<Dimension1 = {"=IsNull(Value1) OR IsNull(Value2) OR IsNull(Value3)" } >} DISTINCT(Dimension1))

• ###### Re: Variable in set analysis not working

Hi stefan

I guess the OP wants the Count where no value is null. so a minus is needed before the = sign i think .

Count({\$<Dimension1 -= {"=IsNull(Value1) OR IsNull(Value2) OR IsNull(Value3)" } >} DISTINCT(Dimension1))

• ###### Re: Variable in set analysis not working

Right, I missed that, but I think you should negate the boolean expression instead:

Count({\$<Dimension1 = {"=not( IsNull(Value1) OR IsNull(Value2) OR IsNull(Value3) )" } >} DISTINCT(Dimension1))

• ###### Re: Variable in set analysis not working

Hi

Try this

Count({\$<Dimension1 ={"= \$(vVariable)"}>} DISTINCT(Dimension1))

• ###### Re: Variable in set analysis not working

I'd do the first part by script

if(IsNull(Value1) OR IsNull(Value2) OR IsNull(Value3), 0, 1)  as _Flag

To the script (where you load all three fields

and then this as Set Analysis

Count({<_Flag = {'1'}>} DISTINCT(Dimension1))

• ###### Re: Variable in set analysis not working

Thanks everyone for your help. Stefan and Bruno, I tried the set expressions as specified but was unable to get either of them working correctly. Most likely due to some other issue in the model. The modification to the load script as Josefina mentioned is working well. Thanks everyone again for assisting me here.