Auto Calendar Date shows as Null in Chart dimension

[odassier]

Hello all,

I'm fairly new to Qlik Sense but have been learning fast thanks to this active community.  I wanted to start playing with set analysis and dates in particular.  To start I correctly (I think) formated my data in excel and loaded it in Qlik Sense.  The Date field was automatically recognized and an auto calendar script loaded by the application.

Next I confirmed in the Asset Fields the "Date" field could be expanded to select "Year", "Quarter", "YearQuarter", etc. So the auto calendar worked.

Now here comes the rub.  When I select "YearMonth" as the dimension, then add a measure, the chart does not display any dates.  What am I doing wrong?  I am attaching the simple qvf file here in the hope someone can correct me or let me know what I'm doing wrong.  I tried it on another existing app and now all my timeline charts are blank!

Much obliged,

Olivier

[jonathandienst]

Do your Excel dates have a time component in them? If so, you may have made the common mistake of using Date() to format them as dates, but this does not remove the time component from the underlying value, The calendar does not have the time component in the Date field, so your data and the calendar never match.

If that is indeed the problem, use this to format the date field AND remove the time component:

    Date(Floor(<your datetime field>)) as <result date field>;

(Replace the placeholders with the correct field name(s))

BTW, the time component can be extracted with

    Time(Frac(<your date field>)) as <result time field>;

[jonathandienst]

The above assumes that model is indeed correct. You probably know this already, but in case you don't - the date field in the calendar must have the same field name as the date field in your data (case sensitive).

[odassier]

HI Jonathan,

Thanks for taking the time to help.  My date does not contain a time component, just day/month/year.

With regards to "the date field in the calendar must have the same field name as the date field in your data (case sensitive)", let's assume I don't know this.  How do I confirm if this is indeed the case since the fields were created automatically by Qlik Sense?  Can I just toggle so a different choice in the data manager and retry?

Cheers,

Olivier

[jonathandienst]

If it was auto-generated, then it is probably correct. You can verify this by opening the table viewer and checking the table structure. if they are associated, there will be a line connecting the tables and the associating field name will be the same in both tables.

[odassier]

HI Jonathan,

Yes, they are, everything checks out as you mentioned above on the data side.  Now my question is when I try to use one of the auto.calendar components (e.g. auto.date.YearMonth) as a dimension, the chart does not display anything (as in the app I attached to my original question).

Cheers,

Olivier

[jonathandienst]

You may need to upload a small sample qvf file that illustrates the problem. Unfortunately, I do not have QS to hand on this workstation, but someone else may be able to help uncover the problem.

[odassier]

I have one in the original message.  I am hoping someone can correct it for me, letting me know what I'm doing wrong and repost it here so I can use it in my full app.

thanks,

Olivier

[odassier]

Anyone has an answer to this question or can correct my .qvf?

[Patrik_Lundblad]

Hi Olivier,

Looks like the in data hasn't been parsed correctly. The easiest way to see that is by dragging out the Date field on the sheet to create a list of all values. That list is currently empty.

Since I don't have the source data I can't see in the data manager what your in data looks like or how it's parsed. But you can check it yourself in the data manager.

So go to data manager, change to table view.

Click the table named Qlik11 and then "edit this table" (ignore that the picture is showing another dataset as I couldn't use yours)

You will then get a view like this, but with your data

Most likely the Date column is just empty?

If it is empty press the calendar icon to open up the date settings

Now change the input format to the correct one. Once it's correct the dates will be shown in the column.

If the dates are already shown let me know, then it's anther issue.

Cheers,

Patrik.