Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 <<)
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
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 <<)
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
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
Yes, my bad
If(Date#([Created Date], 'MMM DD, YYYY') < Date#([QA Approval date], 'MMM DD, YYYY'), 1, 0) as LateApproved
Try this
If(Date#([Due Date], 'MMM DD, YYYY') < Date#([Approved date], 'MMM DD, YYYY'),1,0) as Due_Count
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
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