6 Replies Latest reply: Oct 28, 2016 10:43 AM by Anand kumar barri RSS

    If expression error

    Anand kumar barri

      Hi Guys,

       

      Could somebody help me why this if expression is not working , is it because qlik doesn't understand the > (greater than )  and <(Less than) symbol?

       

      aggr(

      if(((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

      /sum({<Year={$(=max(num(Year))-1)}>}Revenue))<= -1 ,'Lost (100%)',

       

       

      if(((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

      /sum({<Year={$(=max(num(Year))-1)}>}Revenue)) <= -0.99

      and

      ((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

      /sum({<Year={$(=max(num(Year))-1)}>}Revenue))>= -0.70,'Attrition ( 70% -90%)',

       

       

      if(((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

      /sum({<Year={$(=max(num(Year))-1)}>}Revenue)) >= -0.29

      and

      if((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

      /sum({<Year={$(=max(num(Year))-1)}>}Revenue))<= -0.69,'At Risk (20%-69%)',

       

       

      if(((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

      /sum({<Year={$(=max(num(Year))-1)}>}Revenue))=0,'New Clients','Client Growth (>20%)')))),

      [Hqtrs Name])

       

       

      Thanks

      Anand

        • Re: If expression error
          Fei Xu

          try this:

           

           

           

          =if(aggr((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name])<= -1 ,
          'Lost (100%)',
          if(aggr((sum({<Year= {
          $(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name]) <= -0.99
          and
          aggr((sum({<Year= {
          $(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name])>= -0.70,
          'Attrition ( 70% -90%)',
          if(aggr((sum({<Year= {
          $(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name]) >= -0.29
          and
          aggr((sum({<Year= {
          $(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name])<= -0.69,
          'At Risk (20%-69%)',
          if(aggr((sum({<Year= {
          $(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name])=0,
          'New Clients',
          'Client Growth (>20%)'
          )
          )
          )
          )

            • Re: If expression error
              Anand kumar barri

              Thanks for taking time to reply , unfortunately that didnt worked  Fei Xu

                • Re: If expression error
                  Rupam Das

                  What is the error you are getting.

                    • Re: If expression error
                      Anand kumar barri

                      @@Got it !, But I Have an other issue now

                       

                      The error is due to

                       

                      -ve value i.e it should be >= -0.99 inspite of  <= '-0.99' the symbol should be reverse for the -ve values.

                       

                      I have an other issue now, this is aggregating the value based on the max year for individual record for eg:

                      If A is having Revenue data for  2016, 2015, 2014 and B is having data for 2015, 2014

                      the above aggregation is happening at their Max year levels

                       

                      like for A it is picking 2016 revenue and for B it is picking 2015 revenue and the value i'm getting is wrong

                       

                      So If I select 2016 in list box it should sum for only 2016 and should take revenue for  A and take 0 value for B

                       

                      how is this possible

                       

                      the expression im using is

                       

                      (sum({<Year#= {$(=Max(num(Year#)))}>}Revenue)-Sum({<Year#={$(=max(Num(Year#))-1)}>}Revenue)

                       

                       

                      Kind Regards,

                      Anand.

                • Re: If expression error
                  Shraddha Gajare

                  Try

                   

                  aggr(

                  if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))<= -1 ,'Lost (100%)',

                   

                   

                  if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue)) <= -0.99

                  and

                  ((sum({<Year= {"$(=Max(num(Year)))}">}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)}">}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))>= -0.70,'Attrition ( 70% -90%)',

                   

                   

                  if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue)) >= -0.29

                  and

                  if((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))<= -0.69,'At Risk (20%-69%)',

                   

                   

                  if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))=0,'New Clients','Client Growth (>20%)')))),

                  [Hqtrs Name])

                   

                   

                  or

                   

                  aggr(

                  if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))<=' -1' ,'Lost (100%)',

                   

                   

                  if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue)) <= '-0.99'

                  and

                  ((sum({<Year= {"$(=Max(num(Year)))}">}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)}">}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))>= '-0.70','Attrition ( 70% -90%)',

                   

                   

                  if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue)) >= '-0.29'

                  and

                  if((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))<= '-0.69','At Risk (20%-69%)',

                   

                   

                  if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

                  /sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))='0','New Clients','Client Growth (>20%)')))),

                  [Hqtrs Name])

                  • Re: If expression error
                    Teis thrane Wamsler

                    Hi Anand

                     

                    Try testing one step a the time in each If(), to find the line that is uncorrect.

                     

                    /Teis