Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Date Value displays a null

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/201530/06/2016
18/08/201530/06/2016
18/08/201530/06/2016
18/08/201530/06/2016
13/08/201512/07/2016
13/08/201512/07/2016

Here is the image from my qlik sense sheet

2016-11-07_10-22-42.jpg

Just to verify that the invoices displayed  have close values

  Invoice Number

264249
264249 

date opened                    date closed

13/08/201512/07/2016
17/09/201529/03/2016

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

4 Replies

Re: Date Value displays a null

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

Not applicable

Re: Date Value displays a null

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

Re: Date Value displays a null

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

xufei123
Valued Contributor

Re: Date Value displays a null

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]