Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Excel column with a formula is showing as null in Qlik Sense visualizations

I am getting null values for the dates when I have an Excel document that has the date as a long form number(1.43E+12) so I create a new column and calculate the date stamp (=((K5+(-4*3600000))/86400000)+25569). The rows with the calculated fields show as in Qlik Sense. So when I sort by date these records are immediaely excluded.  I copied my formula column in Excel and the pasted it as values and made sure the cell format was "date". However, it does not change what happens when I load it into Qlik Sense.

When I look at the Data Manager or the Data Model Viewer it shows the fields find and validates each row has a value. It is just when I put it into a visualization the value disappears.

Any thoughts?

Message was edited by: michael dove

7 Replies
Employee
Employee

Re: Excel column with a formula is showing as null in Qlik Sense visualizations

could you post the excel document... even if the sample just contains one cell. I'd be curious to test .

Not applicable

Re: Excel column with a formula is showing as null in Qlik Sense visualizations

Thanks Jonathan, I just added a sample of the Excel that I am using where I see the issue.

Employee
Employee

Re: Excel column with a formula is showing as null in Qlik Sense visualizations

Sorry for the delay. I was just testing in 2.0.2

i'm not sure i know how to reproduce the issue.... do you see something different to what i see below ?

Capture.PNG

Not applicable

Re: Excel column with a formula is showing as null in Qlik Sense visualizations

Jonathan,

I figured out that I had the DATE and FLOOR incorrect in my LOAD script. I did see the dates in my Data Manager preview, but never in the visualizations. I am not sure how you did that.

janardhan
Contributor

Re: Excel column with a formula is showing as null in Qlik Sense visualizations

Hi Michael ,

I just loaded what you have given the excel sheet and i'm not getting the null values.

Capture2.PNG

Capture1.PNG

Employee
Employee

Re: Excel column with a formula is showing as null in Qlik Sense visualizations

hmm... i find myself in a converse situation (where i'm not sure how you did the opposite !)

Could you upload the QVF where you see the problem ?

For the desktop softeware, the QVF is located under c:\users\<user>\qlik\sense\apps\

On the server, you can log into the QMC, go to Apps, select the app and from the menu at the bottom of the screen, select 'export'.

Not applicable

Re: Excel column with a formula is showing as null in Qlik Sense visualizations

I appreciate you looking at it for me. I am using an enterprise version of Qlik Sense, not a local. Nevertheless, it is solved now, the dates now display correctly. I wanted to make sure there not something unique I needed to do, and apparently there wasn't anything.

Community Browser