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

[sspawar88]

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

[m_woolf]

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.

[settu_periasamy]

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)