Nested If Condition not working

[#1]

Hey guys for what reason QLik cannot calculate this expression. It works in a straight table but when it is loaded in pivot table it does not work.

any ideas?

=if(commpcnt1<>commpcnt,if(commpcnt1=0.01,commpcnt1=0,(commpcnt1/100)),  
  if(rep1=99 and left(seas_name,2)='SP',0.12,  
    if("Total Discount%"*100<=discord1,(commpcnt/100),  
      (if("Total Discount%"*100>discord1 AND "Total Discount%"*100<=discord2 or discord1>discord2,((commpcnt-reduce1)/100),  
        (if("Total Discount%"*100>discord2 AND "Total Discount%"*100<=discord3 or discord2>discord3,((commpcnt-reduce2)/100),  
           (if("Total Discount%"*100>discord3 AND "Total Discount%"*100<=discord4 or discord3>discord4,((commpcnt-reduce3)/100),  
             (if("Total Discount%"*100>discord4 AND "Total Discount%"*100<=discord5 or discord4>discord5,((commpcnt-reduce4)/100),  
                 (if("Total Discount%"*100>discord5 AND "Total Discount%"*100<=discord6 or discord5>discord6,((commpcnt-reduce5)/100),  
                    (if("Total Discount%"*100>discord6 AND "Total Discount%"*100<=discord7 or discord6>discord7,((commpcnt-reduce6)/100),  
                       (if("Total Discount%"*100>discord7,((commpcnt-reduce7)/100))))))))))))))))))  

[Peter_Cammaert]

In a pivot table, rows can be collapsed, leading to some or all dimension fields in your nested IF producing multiple values. An expression treats an operand that produces multiple values as NULL, and this propagates in weird ways.

Now my turn to ask a question : what do the multiple occurrences of "Total Discount%" refer to? QlikView treats things like that as a literal text string, but your trying to multiply this spec by 100 makes me think that you try to refer to a field that has spaces in its name. You should use [Total Discount%] to refer to such a field, as text strings cannot be used in numerical expressions.

Best,

Peter

[sunny_talwar]

Not sure if this is a typo or if that is what causing the issue:

if(commpcnt1=0.01, commpcnt1=0,(commpcnt1/100)), needs to be this may be

if(commpcnt1=0.01 and/or commpcnt1=0,(commpcnt1/100)) or

if(commpcnt1=0.01, If(commpcnt1=0,(commpcnt1/100))

1. =if(commpcnt1<>commpcnt,if(commpcnt1=0.01,commpcnt1=0,(commpcnt1/100)),
2.   if(rep1=99 and left(seas_name,2)='SP',0.12,   
3.     if("Total Discount%"*100<=discord1,(commpcnt/100),   
4.       (if("Total Discount%"*100>discord1 AND "Total Discount%"*100<=discord2 or discord1>discord2,((commpcnt-reduce1)/100),   
5.         (if("Total Discount%"*100>discord2 AND "Total Discount%"*100<=discord3 or discord2>discord3,((commpcnt-reduce2)/100),   
6.           (if("Total Discount%"*100>discord3 AND "Total Discount%"*100<=discord4 or discord3>discord4,((commpcnt-reduce3)/100),   
7.             (if("Total Discount%"*100>discord4 AND "Total Discount%"*100<=discord5 or discord4>discord5,((commpcnt-reduce4)/100),   
8.                 (if("Total Discount%"*100>discord5 AND "Total Discount%"*100<=discord6 or discord5>discord6,((commpcnt-reduce5)/100),   
9.                     (if("Total Discount%"*100>discord6 AND "Total Discount%"*100<=discord7 or discord6>discord7,((commpcnt-reduce6)/100),   

10.                       (if("Total Discount%"*100>discord7,((commpcnt-reduce7)/100))))))))))))))))))

[Peter_Cammaert]

1st class eyesight, Sunny. I didn't even see that one...

Maybe the best thing Rajat can do is simplify the expression. If only to improve maintainability...

Peter

[sunny_talwar · Accepted solution]

Thanks Peter

I agree, its a very complicated if statement and simplifying it would be very advisable Rajats.1709‌

[#6]

hi Peter thanks for the advice! about your question, "Total Discount%" refers to another column with an expression. The goal of this operation is to calculate commission rates, therefore multiple expressions are used to do this in the QVD below you will see that Actual Comm%, Total Discount%, Amount after discount are used to calculate the TOTAL Comm Amount column.

That is the ultimate goal but when turned into a pivot table the functions do not work as they rely on the columns and they produce null values which mess with the subtotals and the grand totals. Rendering the whole document useless for its purpose.

Do you think you can help with this problem? pcammaert‌ sunindia‌

[#7]

Hey Sunindia!

Glad to hear from you. It is extremely complicated and so hard to maintain for any changes. Is there a way to simplify all this. With my current knowledge i see no way. Maybe you and pcammaert‌ can help?

Thanks

Rajat