topic Re: OR in QlikView

OR

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

if([Detailed status] = 'On Hold - M', ceil(if([Delivered Date] < Opened, Opened, [Delivered Date]) - [Opened])) as [Days Onhold],

But if Delivered date is blank what can I do to get my days on hold? Delivered Date < Opened OR Delivered Date <> ''..... How can I write that IF statement?

thx

jude

Re: OR

sunny_talwar — Tue, 26 Dec 2017 20:31:25 GMT

May be this

If([Detailed status] = 'On Hold - M',

Ceil(If([Delivered Date] < Opened or Len(Trim([Delivered Date])) > 0, Opened, [Delivered Date]) - [Opened])) as [Days Onhold],

Re: OR

Anonymous — Tue, 26 Dec 2017 20:39:21 GMT

Works but just realized if Delivered Date is blank I don’t get any number for Days On Hold. So, how would I add if Delivered Date is blank then today – opened?

Jude Shoop

Sr. Analyst – Technical Information Applications

Arkema Inc.

900 First Avenue

King Of Prussia, PA 19406

Office: 610.205.7194

Mobile: 610.304.5608

Re: OR

sunny_talwar — Tue, 26 Dec 2017 20:44:54 GMT

Something like this may be

If([Detailed status] = 'On Hold - M',

Ceil(

If(Len(Trim([Delivered Date])) = 0, Today(),

If([Delivered Date] < Opened, Opened, [Delivered Date])) - [Opened])) as [Days Onhold],

Re: OR

Anonymous — Tue, 26 Dec 2017 20:55:21 GMT

Thank you, thank you, thank you!!!!!!!!!!!!!!!

Re: OR

sunny_talwar — Tue, 26 Dec 2017 20:56:16 GMT

No problem at all... If you got what you wanted, please close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: OR

Anonymous — Tue, 26 Dec 2017 21:05:07 GMT

Actions just hangs up on me.....

Re: OR

Anonymous — Tue, 26 Dec 2017 22:30:57 GMT

Sunny,

I hate being stupid…..

Based on the On – Hold – M code you helped me with, do my Days Outstanding & Duration Days look correct? (I am thinking they are not)

if(Not match(, 'Closed Canceled', 'Closed Completed','delivered', 'Delivered -A','Request Rejected - A'), today() - ) as ,

if(match(, 'delivered', 'Delivered -A','Closed Completed'), ceil(if( < Opened, Opened, ) - )) as ,

If( = 'On Hold - M',

Ceil(

If(Len(Trim()) = 0, Today(),

If( < Opened, Opened, )) - )) as ,

Jude Shoop

Sr. Analyst – Technical Information Applications

Arkema Inc.

900 First Avenue

King Of Prussia, PA 19406

Office: 610.205.7194

Mobile: 610.304.5608

Re: OR

sunny_talwar — Tue, 26 Dec 2017 22:42:59 GMT

You work near King Of Prussia? I am in Valley Forge .

Coming back to your question... not sure I understand what you looking to get?

Re: OR

Anonymous — Tue, 26 Dec 2017 22:51:42 GMT

Yup, Hi neighbor

Ok so I have my number of hold days, now I need to know days outstanding & duration days.

If it's not on hold, not closed canceled, not closed complete, not delivered, how many days outstanding.

if(Not match([Detailed status], 'Closed Canceled', 'Closed Completed','delivered', 'Delivered -A','Request Rejected - A'), today() - [Opened]) as [Days Outstanding],

IF it's delivered, delivered -A, Closed Completed, how many days did it take?

if(match([Detailed status], 'delivered', 'Delivered -A','Closed Completed'), ceil(if([Delivered Date] < Opened, Opened, [Delivered Date]) - [Opened])) as [Duration (Days)],

Hope this makes sense

Re: OR

Anonymous — Wed, 27 Dec 2017 18:34:02 GMT

This isn’t working….. )/30 as ;

What am I missing?

Jude Shoop

Sr. Analyst – Technical Information Applications

Arkema Inc.

900 First Avenue

King Of Prussia, PA 19406

Office: 610.205.7194

Mobile: 610.304.5608

Re: OR

Anonymous — Wed, 27 Dec 2017 19:23:25 GMT

Not sure of what posted above....

How can I calculate time outstanding in months based on days onhold? This is what was being done in the old code but it's not working.

[Days Outstanding],

[Duration (Days)],

[Days Onhold])/30 as [Time Outstanding in Months];