Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
sparkerb
New 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

Tags (1)
1 Solution

Accepted Solutions
jsf_fasoli
Contributor II

Re: Variable in set analysis not working

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

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

brunobertels
Valued Contributor II

Re: Variable in set analysis not working

Hi

Try this

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



jsf_fasoli
Contributor II

Re: Variable in set analysis not working

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

sparkerb
New Contributor II

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.

pradosh_thakur
Honored Contributor II

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

Learning never stops.
MVP
MVP

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