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.

Not applicable

Creation of a formula invoving blanks

Hi

I have created a (straight table on the front end) which is shown below.

I am struggling  to get the comment part right.

My formula should read as follows

If the "Delvery Date" is greater than the "Expected Delivery Date",than the result should be "Late Delivery" , if not it should be "On Time".

If the "Delivery Date" is blank, than the result should be blank, however i get the comment "On Time".  This is incorrect (ie On Time should not be the result if delivery date is blank)

Here is my formula:

if([Delivery Date]=' ',' ',if([Delivery Date]>[Expected Delivery Date],'Late Delivery','On Time'))

Thank you

kind regards

Nayan

Invoice NumberOrder DateInvoice DateExpected Delivery DateDelivery DateComment
IN00000006739944-Mar-137-Mar-1315-Mar-13-On Time
IN00000006740004-Mar-137-Mar-1315-Mar-13-On Time
IN00000006750174-Mar-1312-Mar-1315-Mar-13-On Time
IN00000006750184-Mar-1312-Mar-1315-Mar-13-On Time
IN00000006750204-Mar-1312-Mar-1315-Mar-13-On Time
IN000000067554612-Mar-1314-Mar-1312-Mar-13-On Time
IN000000067658218-Mar-1319-Mar-1329-Mar-13-On Time
IN000000067676212-Mar-1320-Mar-1322-Mar-13-On Time
IN000000067676312-Mar-1320-Mar-1322-Mar-13-On Time
IN000000067707612-Mar-1322-Mar-1312-Mar-13-On Time
IN000000067776018-Mar-1327-Mar-1329-Mar-13-On Time
IN000000069408319-Jun-1320-Jun-1325-Jun-1327-Jun-13Late Delivery
IN000000069553026-Jun-1327-Jun-1326-Jun-1328-Jun-13Late Delivery
IN000000069555226-Jun-1327-Jun-1326-Jun-1327-Jun-13Late Delivery
IN000000069566920-Jun-1327-Jun-1325-Jun-132-Jul-13Late Delivery
IN000000069572720-Jun-1328-Jun-1325-Jun-132-Jul-13Late Delivery
IN00000006960441-Jul-131-Jul-135-Jul-132-Jul-13On Time
IN00000006961031-Jul-131-Jul-135-Jul-132-Jul-13On Time
IN00000006961071-Jul-131-Jul-135-Jul-132-Jul-13On Time
IN00000006978108-Jul-1310-Jul-1315-Jul-1317-Jun-13On Time
IN000000069937617-Jul-1317-Jul-1330-Jul-1324-Jun-13On Time
IN00000007031201-Aug-132-Aug-135-Aug-137-Jun-13On Time
IN00000007038235-Aug-136-Aug-138-Aug-138-Jun-13On Time
1 Solution

Accepted Solutions
rajeshvaswani77
Valued Contributor III

Re: Creation of a formula invoving blanks

Try this:


if(isnull([Delivery Date]),' ',if([Delivery Date]>[Expected Delivery Date],'Late Delivery','On Time'))

thanks,

Rajesh Vaswani

4 Replies
MVP
MVP

Re: Creation of a formula invoving blanks

Try this:


if(Len(Trim([Delivery Date]))=0,' ',if([Delivery Date]>[Expected Delivery Date],'Late Delivery','On Time'))

For better understanding have a look here

rajeshvaswani77
Valued Contributor III

Re: Creation of a formula invoving blanks

Try this:


if(isnull([Delivery Date]),' ',if([Delivery Date]>[Expected Delivery Date],'Late Delivery','On Time'))

thanks,

Rajesh Vaswani

Not applicable

Re: Creation of a formula invoving blanks

Hi Rajesh

It worked . Thank you.

Kind regards

Nayan

Not applicable

Re: Creation of a formula invoving blanks

Hi Tresesco

Your formula worked too.

Thank you

Kind regards

Nayan