topic Re: Compare dates and mark Late in QlikView

Compare dates and mark Late

Tue, 21 Jan 2014 09:58:54 GMT

Hi, I wonder if you could help.

I have two dates in my qlikview app a Collection date and Delivery date I can work out the numbers of days between these by doing a : expression like so: =dayStart(Deldate) - DayStart(timestamp(CollDate))

however I want to compare the date with a service level if the service level is EC and the Date is over 3 days mark it as late if its 3 days mark it as Ontime and if its earlier than 3 Mark it Early

Then I would like to do the same with a Different Service but different parameters and display this on a graph showing what's on time and what's late and what's early

what's the best way of doing this? does anyone have any examples?

thanks in advance

Re: Compare dates and mark Late

richard — Tue, 21 Jan 2014 10:08:08 GMT

Try this in you script or object expression:

if(Service= 'A',
(if(dayStart(Deldate) - DayStart(Deldatetimestamp(CollDate))=3, 'On time',
if(dayStart(Deldate) - DayStart(Deldatetimestamp(CollDate))<3, 'Early',
if(dayStart(Deldate) - DayStart(Deldatetimestamp(CollDate))>3, 'Late')))),
Service= 'B',
(if(dayStart(Deldate) - DayStart(Deldatetimestamp(CollDate))=3, 'On time',
if(dayStart(Deldate) - DayStart(Deldatetimestamp(CollDate))<3, 'Early',
if(dayStart(Deldate) - DayStart(Deldatetimestamp(CollDate))>3, 'Late'))))
)

Re: Compare dates and mark Late

Tue, 21 Jan 2014 10:32:47 GMT

Hi Richard,

I seem to be getting an error with the above so I changed it to: = if(Service= 'EC', (if(dayStart(Deldate) - DayStart(timestamp(CollDate))=3, 'On time', if(dayStart(Deldate) - DayStart(timestamp(CollDate))3, 'Late')))), Service= 'SD', (if(dayStart(Deldate) - DayStart(timestamp(CollDate))=1, 'On time', if(dayStart(Deldate) - DayStart(timestamp(CollDate))1, 'Late')))) ) but then I just get a - in the table even though it says the expression is correct?

Re: Compare dates and mark Late

jagan — Tue, 21 Jan 2014 10:48:07 GMT

Hi Jason,

Try like this

LOAD

If(DaysDifference = 3, 'On time', If(DaysDifference > 3, 'Late', If(DaysDifference < 3, 'Early', 'N/A') AS Status;

LOAD

dayStart(Deldate) - DayStart(timestamp(CollDate)) AS DaysDifference

FROM DataSource;

Regards,

Jagan.

Re: Compare dates and mark Late

richard — Tue, 21 Jan 2014 10:48:30 GMT

Ofcourse you should also replace the term "Service" by you own field that contains the kind of service to check,