Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

more than one 'if' in expression?

Hello!

I was wondering if it was possible to have more than one if in the expression of a gauge chart (or any other chart, for that matter), something like this:

if(isnull(GetFieldSelections(Field_C) and isnull(GetFieldSelections(Field_B) and isnull(GetFieldSelections(Field_A), Sum(Value_E))
if(isnull(GetFieldSelections(Field_A)), Sum(Value_E), num(GetFieldSelections(Field_A)) * Sum(Value_E))
if(isnull(GetFieldSelections(Field_B)), Sum(Value_E), num(GetFieldSelections(Field_B)) * Sum(Value_E))
if(isnull(GetFieldSelections(Field_C)), Sum(Value_E), num(GetFieldSelections(Field_C)) * Sum(Value_E))




The gauge result will depend on what values are selected in fields A, B and C, and if no value is selected on these fields, then I just want it to display sum(Value_E).

The only alternative I see here is a very complicated set of chained if's...

Thanks!

Alexandra

Tags (4)
1 Solution

Accepted Solutions
MVP
MVP

more than one 'if' in expression?

Something like this perhaps:

sum(Value_E)
* rangemax(1,num(GetFieldSelections(Field_A)))
* rangemax(1,num(GetFieldSelections(Field_B)))
* rangemax(1,num(GetFieldSelections(Field_C)))

If there are no field selections, I believe that part returns null. Rangemax() treats null as 0, so would then return 1, which means that portion of the calculation would do nothing, just as if the IF hadn't fired. So I don't think you need a complicated IF expression at all.

5 Replies
stephen-a_redmo
Valued Contributor II

more than one 'if' in expression?

Hi Alexandra,

It doesn't have to be too complicated.


=if(isnull(GetFieldSelections(Field_C)) and isnull(GetFieldSelections(Field_B)) and isnull(GetFieldSelections(Field_A)), Sum(Value_E),
if(not isnull(GetFieldSelections(Field_A)), num(GetFieldSelections(Field_A)) * Sum(Value_E),
if(not isnull(GetFieldSelections(Field_B)), num(GetFieldSelections(Field_B)) * Sum(Value_E),
if(not isnull(GetFieldSelections(Field_C)), num(GetFieldSelections(Field_C)) * Sum(Value_E)
))))


Note that this will not work if more than one item is selected in A, B, or C because GetFieldSelections returns a comma separated list.

Regards,

Stephen

Not applicable

more than one 'if' in expression?

Hi Stephen, thanks for your answer,

Indeed that expression doesn't seem very complicated, but the problem is the following: Values A, B and C start with no value selected. Then, on the course of the application use, the user selectes one value from Value_A and then another from Value_B (these are slider objects, btw, and so is Value_C).

So, you see, when one from Value_A is selected, the gauge calculates the expression, 'num(GetFieldSelections(Field_A)) * Sum(Value_E)', but then the user selects a value fom Value_B, with Value_A still being selected and QV is still going only for the first 'else' in the expression, not evaluating if Value_B has any selection.

What I want is for QV to take into account that Value_A is selected as well as Value_B (and possibly even Value_C) and so I would like it to calculate 'num(GetFieldSelections(Field_A)) * Sum(Value_E)' and then again ' * num(GetFieldSelections(Field_B))'. That's why I want several if's, so QV can evaluate all the three values A, B and C...

Chained if's will lead QV to evaluate only the first that has any value.

kind of like

int i = Sum(Value_E)
if(not isnull(GetFieldSelections(Field_A)))
i *= num(GetFieldSelections(Field_A))
if(not isnull(GetFieldSelections(Field_B)))
i *= num(GetFieldSelections(Field_B))
if(not isnull(GetFieldSelections(Field_C))
i *= num(GetFieldSelections(Field_C))


stephen-a_redmo
Valued Contributor II

more than one 'if' in expression?

Hi Alexandra,

Not too difficult to code. Just looks long:


=if(isnull(GetFieldSelections(Field_C)) and isnull(GetFieldSelections(Field_B)) and isnull(GetFieldSelections(Field_A)), Sum(Value_E),
if(not isnull(GetFieldSelections(Field_A)) and isnull(GetFieldSelections(Field_B)) and isnull(GetFieldSelections(Field_C)), num(GetFieldSelections(Field_A)) * Sum(Value_E),
if(not isnull(GetFieldSelections(Field_A)) and not isnull(GetFieldSelections(Field_B)) and isnull(GetFieldSelections(Field_C)), num(GetFieldSelections(Field_B)) * Sum(Value_E),
if(not isnull(GetFieldSelections(Field_C)), num(GetFieldSelections(Field_C)) * Sum(Value_E)
))))


You can include as many ifs as you need.

Stephen

MVP
MVP

more than one 'if' in expression?

Something like this perhaps:

sum(Value_E)
* rangemax(1,num(GetFieldSelections(Field_A)))
* rangemax(1,num(GetFieldSelections(Field_B)))
* rangemax(1,num(GetFieldSelections(Field_C)))

If there are no field selections, I believe that part returns null. Rangemax() treats null as 0, so would then return 1, which means that portion of the calculation would do nothing, just as if the IF hadn't fired. So I don't think you need a complicated IF expression at all.

Not applicable

more than one 'if' in expression?

John, thank you so much, that's just it! Between null and 1, rangemax returns 1, it's perfect

Community Browser