topic Re: Nested If Condition not working in QlikView

Nested If Condition not working

Mon, 20 Jul 2015 13:24:46 GMT

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

Re: Nested If Condition not working

Peter_Cammaert — Mon, 20 Jul 2015 13:35:54 GMT

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

Re: Nested If Condition not working

sunny_talwar — Mon, 20 Jul 2015 13:39:10 GMT

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

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

Re: Nested If Condition not working

Peter_Cammaert — Mon, 20 Jul 2015 13:50:57 GMT

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

Re: Nested If Condition not working

sunny_talwar — Mon, 20 Jul 2015 14:04:22 GMT

Thanks Peter

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

Re: Nested If Condition not working

Mon, 20 Jul 2015 15:33:16 GMT

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‌

Re: Nested If Condition not working

Mon, 20 Jul 2015 15:35:03 GMT

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