Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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

1 Solution

Accepted Solutions
Employee
Employee

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

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

8 Replies
Employee
Employee

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

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

Not applicable

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

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

Employee
Employee

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

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

Not applicable

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

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

Employee
Employee

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

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

Not applicable

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

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

Employee
Employee

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

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

Not applicable

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


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