topic Re: Can I count dates not reached deadline? in QlikView

Can I count dates not reached deadline?

Anonymous — Tue, 04 Aug 2015 06:46:21 GMT

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

Re: Can I count dates not reached deadline?

Anonymous — Tue, 04 Aug 2015 06:57:18 GMT

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

Re: Can I count dates not reached deadline?

jonathandienst — Tue, 04 Aug 2015 07:05:03 GMT

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

Re: Can I count dates not reached deadline?

richard_chilvers — Tue, 04 Aug 2015 07:06:42 GMT

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

Re: Can I count dates not reached deadline?

jonathandienst — Tue, 04 Aug 2015 07:07:04 GMT

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

Re: Can I count dates not reached deadline?

jonathandienst — Tue, 04 Aug 2015 07:07:47 GMT

Yes, my bad

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

Re: Can I count dates not reached deadline?

qlikmsg4u — Tue, 04 Aug 2015 07:08:34 GMT

Try this

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

Re: Can I count dates not reached deadline?

Kushal_Chawda — Tue, 04 Aug 2015 07:14:57 GMT

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

Re: Can I count dates not reached deadline?

Anonymous — Tue, 04 Aug 2015 09:35:10 GMT

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