Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sparkerb
Contributor II
Contributor II

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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

View solution in original post

6 Replies
swuehl
MVP
MVP

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

Hi

Try this

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



Anonymous
Not applicable

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
Contributor II
Contributor II
Author

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
Master II
Master II

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

Learning never stops.
swuehl
MVP
MVP

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