topic more than one 'if' in expression? in QlikView

more than one 'if' in expression?

Mon, 13 Jul 2009 17:03:44 GMT

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

more than one 'if' in expression?

stephencredmond — Mon, 13 Jul 2009 17:15:47 GMT

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

more than one 'if' in expression?

Mon, 13 Jul 2009 18:09:36 GMT

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

more than one 'if' in expression?

stephencredmond — Mon, 13 Jul 2009 18:42:33 GMT

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

more than one 'if' in expression?

johnw — Tue, 14 Jul 2009 02:18:18 GMT

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.

more than one 'if' in expression?

Tue, 14 Jul 2009 15:54:36 GMT

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