6 Replies Latest reply: Jul 20, 2015 11:36 AM by Rajat Sharma RSS

    Nested If Condition not working

      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

          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

              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 stalwar1

            • Re: Nested If Condition not working
              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))))))))))))))))))