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

[#1]

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

[JonnyPoole]

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

[#3]

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

[JonnyPoole]

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

[#5]

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

[JonnyPoole]

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

[#7]

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

[JonnyPoole · Accepted solution]

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

[#9]

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