Date Value displays a null

[#1]

All;

  I think that this is a very simple question but I cannot seem to identify the issue.

  I have an excel file that has several columns in it 2 of which are open date and closed date.

  I wanted to put the 2 dates into a table so I could then calculate the interval or the length of time to get to a close.

  The trouble I seem to be having is that the open date displays normally but the close date displays as null.  I cannot seem to determine why the close date displays as null.  The format of the column is  Date  and has the same type of data as the open date.

  Date Open                    Date Closed

05/05/2015	30/06/2016
18/08/2015	30/06/2016
18/08/2015	30/06/2016
18/08/2015	30/06/2016
13/08/2015	12/07/2016
13/08/2015	12/07/2016

Here is the image from my qlik sense sheet

Just to verify that the invoices displayed  have close values

  Invoice Number

264249

264249

date opened                    date closed

13/08/2015	12/07/2016
17/09/2015	29/03/2016

  Thanks in advance for any suggestions -   The  table values have no expressions just a display of the field value

[kaushiknsolanki]

Hi,

Just one question, did you use the Date function while loading, like Date(Closed Date).

If Yes then it is because the Value which you are getting from Excel is not in Date format, its a Text format.

You can try this,

1. Change the source. Go to excel and change the format of the Closed Date to Date format.

2. You can use the Date#() function as below to convert text into Date.

     Date(Date#(Closed Date,'DD/MM/YYYY')) as ClosedDate

Regards,

Kaushik Solanki

[#3]

Thank you for your reply- oddly the excel format is  Date DD/MM/YYYY

and the input script uses 

Date( Date#([Date Closed], 'DD/MM/YYYY' ) , 'DD/MM/YYYY') As [Date Closed],

Ian

[kaushiknsolanki]

Ian,

I agree that the format will be same as you said, but the Data format will be different like Text or Date.

In Qlik If you use the Date() function on Text it will give Null Value.

Also if you use the Date() function on Date it may give you wrong value.

So just load as it comes from excel and see if it works without any function.

If still doesnt work share the excel with us with script you are using.

Regards,

Kaushik Solanki

[NZFei]

1) What is the default date format in Sense script?

2) What is the result if you change the input script to just

Date([Closed Date]) as [Closed Date]