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.

petersvendsen
Not applicable

Can I count dates not reached deadline?

Hi all,

I would like to make a chart that show s numbers of cases not reaching the deadline.

In my xls file I have a columb called "Due date" and a columb called "Approved date" so if the "Approved date" is not done before the "due date" it should cound as 1.

I was considering as expression under the chart something like

Count({<[Due date]>>[Approved date]>})

But I get an error....

Thanks

Peter

1 Solution

Accepted Solutions
jontydkpi
Not applicable

Re: Can I count dates not reached deadline?

You can't use a set expression for this. Set expressions are evaluated once for the chart/table and not row by row. So the script solution is the way to go, but your expression is incorrect - it should be:

If(Date#([Created Date], 'MMM DD, YYYY')  < Date#([QA Approval date], 'MMM DD, YYYY')) as LateApproved,

(Date() is a formatting function and is redundant here. Use < and not <<)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
8 Replies
petersvendsen
Not applicable

Re: Can I count dates not reached deadline?

I have also tried to use the following in the scripting area, wirthout luck

 

if((date(date#([Created Date], 'MMM DD, YYYY'), 'MMM DD, YYYY'))<<(date(date#([QA Approval date], 'MMM DD, YYYY'), 'MMM DD, YYYY')), 1, 0) as LateApproved

jontydkpi
Not applicable

Re: Can I count dates not reached deadline?

You can't use a set expression for this. Set expressions are evaluated once for the chart/table and not row by row. So the script solution is the way to go, but your expression is incorrect - it should be:

If(Date#([Created Date], 'MMM DD, YYYY')  < Date#([QA Approval date], 'MMM DD, YYYY')) as LateApproved,

(Date() is a formatting function and is redundant here. Use < and not <<)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
richard_chilvers
Not applicable

Re: Can I count dates not reached deadline?

I may be missing the point. But usually you can compare dates in a normal way ie. IF(Approved_Date < Due_date, 1,0).

This will also depend on what value Approved_date has before item has been approved.

HTH

jontydkpi
Not applicable

Re: Can I count dates not reached deadline?

As a general practice, make sure all dates load as valid dates, then you can use a preceding load to simplify the statement further:

If([Created Date] < [QA Approval date], 1, 0) as LateApproved

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jontydkpi
Not applicable

Re: Can I count dates not reached deadline?

Yes, my bad

If(Date#([Created Date], 'MMM DD, YYYY')  < Date#([QA Approval date], 'MMM DD, YYYY'), 1, 0) as LateApproved

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
qlikmsg4u
Not applicable

Re: Can I count dates not reached deadline?

Try this

If(Date#([Due Date], 'MMM DD, YYYY')  < Date#([Approved date], 'MMM DD, YYYY'),1,0) as Due_Count

kush141087
Not applicable

Re: Can I count dates not reached deadline?

Your if is correct, just remove one extra '<'

if((date(date#([Created Date], 'MMM DD, YYYY'), 'MMM DD, YYYY'))<<(date(date#([QA Approval date], 'MMM DD, YYYY'), 'MMM DD, YYYY')), 1, 0) as LateApproved

petersvendsen
Not applicable

Re: Can I count dates not reached deadline?

Hi all,

Thanks a lot for all the good input and help, I got it to work and can continue the test of the application.

BR,

Peter