topic Re: Why this set analysis for 'Nested if' is not working properly? in QlikView

Why this set analysis for 'Nested if' is not working properly?

sspawar88 — Fri, 12 Jun 2015 06:26:19 GMT

Hello Friends,

i'm try to calculate the percentage of Temperature Compliance for each branch type . it shows the 'OK' in Set analysis but it won't work properly.

can you show me whats the exact mistake in following calculation in set analysis.?

it will not give proper solution in pivot table and cyclic group or drill down.

/* Branch Type ST */

round(

if(branch_type = 'ST',

If(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'ST'}>}param_value))>= 22

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'ST'}>}param_value)))/22,

If((Fabs(Avg({$<param_name={'Temperature'},branch_type = {'ST'}>}param_value)))<22

and (Fabs(Avg({$<param_name={'Temperature'},branch_type = {'ST'}>}param_value))) >=21

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'ST'}>}param_value)))/21.5,

If(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'ST'}>}param_value))<= 20

,(Fabs(avg({$<param_name={'Temperature'},branch_type = {'ST'}>}param_value)))/20))),

/* Branch Type DEPOT */

if(branch_type = 'DEPOT',

If(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'DEPOT'}>}param_value))>= 25

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'DEPOT'}>}param_value)))/25,

If((Fabs(Avg({$<param_name={'Temperature'},branch_type = {'DEPOT'}>}param_value)))<25

and (Fabs(Avg({$<param_name={'Temperature'},branch_type = {'DEPOT'}>}param_value))) >=23

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'DEPOT'}>}param_value)))/23.5,

If(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'DEPOT'}>}param_value))<= 22.9

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'DEPOT'}>}param_value)))/22))),

/* Branch Type RS */

if(branch_type = 'RS',

If(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'RS'}>}param_value))>= 20

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'RS'}>}param_value)))/20,

If((Fabs(Avg({$<param_name={'Temperature'},branch_type = {'RS'}>}param_value)))<20

and (Fabs(Avg({$<param_name={'Temperature'},branch_type = {'RS'}>}param_value))) >=19

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'RS'}>}param_value)))/19.5,

If(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'RS'}>}param_value))<= 18.9

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'RS'}>}param_value)))/18))),

/* Branch Type PT */

if(branch_type = 'PT',

If(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'PT'}>}param_value))>= 25

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'PT'}>}param_value)))/25,

If((Fabs(Avg({$<param_name={'Temperature'},branch_type = {'PT'}>}param_value)))<25

and (Fabs(Avg({$<param_name={'Temperature'},branch_type = {'PT'}>}param_value))) >=23

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'PT'}>}param_value)))/23.5,

If(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'PT'}>}param_value))<= 22.9

,(Fabs(Avg({$<param_name={'Temperature'},branch_type = {'PT'}>}param_value)))/22)

)

))))*100)

Set analysis

Re: Why this set analysis for 'Nested if' is not working properly?

m_woolf — Fri, 12 Jun 2015 12:20:13 GMT

Greatly simplifying:

if(x>=22,Do this,

if(x<22, Do this,

if(x<=20, Do this)))

The third IF will never evaluate. Suppose x = 15. 15 is < 22 so the second IF will handle that value.

Try:

if(x>=22, Do this,

if(x<=20, Do this,

Do this))

If x is not > = 22 and x is not < = 20 then it has to be between 20 and 22. No reason for the third IF.

Re: Why this set analysis for 'Nested if' is not working properly?

settu_periasamy — Fri, 12 Jun 2015 14:02:25 GMT

Hi,

Just create the variable for Better understanding like the below (vST, vDEPOT, vRS and vPT)

vST : Avg({$<param_name={'Temperature'},branch_type = {'ST'}>}param_value)
vDEPOT : Avg({$<param_name={'Temperature'},branch_type = {'DEPOT'}>}param_value)
vRS : Avg({$<param_name={'Temperature'},branch_type = {'RS'}>}param_value)
vPT : Avg({$<param_name={'Temperature'},branch_type = {'PT'}>}param_value)

In the Expression, put the below

=round(Pick(Match(branch_type,'ST','DEPOT','RS','PT'),
  If(Fabs($(vST))>= 22,(Fabs($(vST)))/22,
  If((Fabs($(vST)))<22 and (Fabs($(vST))) >=21,(Fabs($(vST)))/21.5,
  If(Fabs($(vST))<= 20,(Fabs($(vST)))/20))),  

  If(Fabs($(vDEPOT))>= 25,(Fabs($(vDEPOT)))/25,
  If((Fabs($(vDEPOT)))<25 and (Fabs($(vDEPOT))) >=23,(Fabs($(vDEPOT)))/23.5,
  If(Fabs($(vDEPOT))<= 22.9,(Fabs($(vDEPOT)))/22))),
    
  If(Fabs($(vRS))>= 20,(Fabs($(vRS)))/20,
  If((Fabs($(vRS)))<20 and (Fabs($(vRS))) >=19,(Fabs($(vRS)))/19.5,
  If(Fabs($(vRS))<= 18.9,(Fabs($(vRS)))/18))),

  If(Fabs($(vPT))>= 25,(Fabs($(vPT)))/25,
  If((Fabs($(vPT)))<25 and (Fabs($(vPT))) >=23,(Fabs($(vPT)))/23.5,
  If(Fabs($(vPT))<= 22.9,(Fabs($(vPT)))/22))))*100)