8 Replies Latest reply: Aug 26, 2014 11:20 AM by Myles Holland RSS

    Percentage Variance Column showing -100% when it should show 0%

    Myles Holland

      Hello all,

      This is my first post on this community forum. I did search quickly for an answer within the scripting discussion area, but didn't see anything that solved my particular problem. If there is a direction that I can be pointed in I would appreciate that as well.

       

      My script is as follows:

      num(

      (
      if(IsNull(avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime)), 0, avg({$*<CycleType={'Spec to Post'}, [Tender Status]-={'TENDER CANCELLED'}, [Milestone.Flag]={'0'}, [Cycle Status]={'Actual'}>} CycleTime)) / 15.3)-1

      , '#,##0%')

       

      I believe I know as to why this is happening as it relates to the end of my code which asks the query to take the cycletime/15.3-1, which normally would give me the result I desire, but in this case cycle time is 0, so the percentage would be -100%. However, I would like assistance if any of you can offer that will allow me into an addition to the script that basically takes into account that when 0 is present express result as 0% instead.

       

      Again I am new to Qlikview and apologize if this is a newbie question.

       

      Regards,

       

      Hollando

       

       

        • Re: Percentage Variance Column showing -100% when it should show 0%
          Jonathan Poole

          I can see your checking for NULLs but not 0's.

           

          How about if you do a 2nd condition in the IF

           

          (if(

               IsNull(avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime))

               or (avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime)=0

               ,

               0,

               avg({$*<CycleType={'Spec to Post'}, [Tender Status]-={'TENDER CANCELLED'}, [Milestone.Flag]={'0'},[Cycle Status]={'Actual'}>} CycleTime)) / 15.3)-1

            • Re: Percentage Variance Column showing -100% when it should show 0%
              Myles Holland

              Thanks for responding Jonathan.

               

              I seem to be getting an _Error result within the Table now.

               

               

              num(if(IsNull(avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime))

              or (avg({$*<CycleType={'Spec to Post'},
              [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime)= , 0 ,

              avg({$*<CycleType={'Spec to Post'},
              [Tender Status]-={'TENDER CANCELLED'}, [Milestone.Flag]={'0'},[Cycle Status]={'Actual'}>} CycleTime)) / 15.3)-1

              , '#,##0%')

               

              Again I am new to Qlikview. Perhaps the it's how I have inputted the code in the above?

               

              Regards,

               

              Hollando

                • Re: Percentage Variance Column showing -100% when it should show 0%
                  Jonathan Poole

                  2 issues . remove  the leading '(' before the 'or'.  ( i made same mistake!) and add =0 to the same line:

                   

                  num(if(IsNull(avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime))

                  or avg({$*<CycleType={'Spec to Post'},
                  [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime)= 0, 0 ,

                  avg({$*<CycleType={'Spec to Post'},
                  [Tender Status]-={'TENDER CANCELLED'}, [Milestone.Flag]={'0'},[Cycle Status]={'Actual'}>} CycleTime)) / 15.3)-1

                  , '#,##0%')

                    • Re: Re: Percentage Variance Column showing -100% when it should show 0%
                      Myles Holland

                      Hi Jonathan,

                       

                      I thought this would have worked from a logic standpoint.

                       

                      Still getting an _error Value.

                       

                      Script:

                       

                       

                      num(if(IsNull(avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime))

                      or avg({$*<CycleType={'Spec to Post'},
                      [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime)= 0, 0 ,

                      avg({$*<CycleType={'Spec to Post'}, [Tender Status]-={'TENDER CANCELLED'}, [Milestone.Flag]={'0'},[Cycle Status]={'Actual'}>} CycleTime)) / 15.3)-1

                      , '#,##0%')

                       

                      Two things I noticed were the ')' after , '#,##0% is now red and the message at the top of the 'Edit Expression' Dialogue box is "Garbage after expression ",".

                       

                      Regards,

                       

                      Hollando

                        • Re: Re: Percentage Variance Column showing -100% when it should show 0%
                          Jonathan Poole

                          we'll get there !

                           

                          try adding a '(' before the else condition avg:

                           

                          here it is with it added back in there:

                           

                          num(if(IsNull(avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime))

                          or avg({$*<CycleType={'Spec to Post'},
                          [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime)= 0, 0 ,

                          (avg({$*<CycleType={'Spec to Post'}, [Tender Status]-={'TENDER CANCELLED'}, [Milestone.Flag]={'0'},[Cycle Status]={'Actual'}>} CycleTime)) / 15.3)-1

                          , '#,##0%')

                            • Re: Re: Re: Percentage Variance Column showing -100% when it should show 0%
                              Myles Holland

                              Well on plus side the expression is now "ok".

                               

                              The only problem now is that we're back to square one the percent has returned to -100%.

                               

                              =num(if(IsNull(avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime))

                              or avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime)=0, 0,

                              (avg({$*<CycleType={'Spec to Post'},
                              [Tender Status]-={'TENDER CANCELLED'}, [Milestone.Flag]={'0'},[Cycle Status]={'Actual'}>} CycleTime)) / 15.3)-1

                              , '#,##0%')

                              Regards,

                               

                              Myles

                                • Re: Re: Re: Percentage Variance Column showing -100% when it should show 0%
                                  Jonathan Poole

                                  I wrote out my expression in qlik first this time . move some parentheses around and got this. Want to try once more ?

                                   

                                  =num(

                                   

                                    if(

                                    IsNull(avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime))

                                    or

                                    avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime)=0

                                   

                                    ,0

                                    , (avg({$*<CycleType={'Spec to Post'}, [Tender Status]-={'TENDER CANCELLED'}, [Milestone.Flag]={'0'},[Cycle Status]={'Actual'}>} CycleTime) / 15.3)-1

                                   

                                    )

                                  , '#,##0%)

                                    • Re: Re: Re: Re: Percentage Variance Column showing -100% when it should show 0%
                                      Myles Holland


                                      Worked like a charm. One minor change to the script " ' " at the end. I added to below for anyone that wanted to resuse.

                                       

                                      Thank you for help on this Jonathan, and sorry for not replying yesterday.

                                       

                                      =num(if(IsNull(avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime))

                                      or avg({$*<CycleType={'Spec to Post'}, [Milestone.Flag]={'0'}, [Tender Status]-={'TENDER CANCELLED'}, [Cycle Status]={'Actual'}>} CycleTime)=0 , 0,

                                       

                                      (avg({$*<CycleType={'Spec to Post'}, [Tender Status]-={'TENDER CANCELLED'}, [Milestone.Flag]={'0'},[Cycle Status]={'Actual'}>} CycleTime) / 15.3)-1), '#,##0%')

                                       

                                      Regards,

                                       

                                      Hollando