Variable in set analysis not working

[sparkerb]

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

Dimension1	vVariable	Value1	Value2	Value3
AAA	AAA	XXX	YYY	ZZZ
BBB	-	-	YYY	ZZZ
CCC	-	XXX	-	ZZZ
DDD	-	XXX	YYY	-
EEE	EEE	XXX	YYY	ZZZ
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

[swuehl]

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

[brunobertels]

Hi

Try this

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

[Anonymous · Accepted solution]

I'd do the first part by script

Add

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

[sparkerb]

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.

[pradosh_thakur]

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

[swuehl]

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