Qlil Sense Date Query

[malradi88]

Hi All,

I have a query regarding how Qlik sense processes dates in excel and what I can do at the preparatory stage to ensure that certain fields can be transformed from 'general' to 'date' format when I click 'edit this table' and amend  a 'field menu' before loading data.

The dates in my excel files all follow the DD/MM/YYYY format and I double check to see if the format in excel is 'date'. Sometimes I am lucky and I can convert my dates into 'date' format inside qlik before uploading data. This then allows me to analyse dates by day, month, quarter, monthyear, year etc...automatically. But in most cases, when I change the field format from general to date all the data disappears as shown below:

When the field is still in 'General' format the data is in number form (42036 etc...)

When the field changed to 'date' format, the data disappears (keeping in mind that in the excel data source all dates are written dd/mm/yyyyy).

I realise there are Master calender functions that can be added to the data load editor after the data is uploaded, but I usually have problems inserting that script and if there was a way to allow me to change field format prior to uploading data (or at least know why sometimes i can change format from general to date and others times not) that would save a lot of time.

Thank you very much for your help!

Best,

Mohammed

[#2]

Hi Mohammed,

I think you should go to the script and change

                              Date(Date#(DateField,'DD/MM/YYYY')) as DateField

That will automatically force Qlik Sense to interpret your field as a date field every time you'll load new data.

Let me know if I understood

[malradi88]

Hi Nicolo,

Thank you for reply! I tried you script but can't seem to get it working. Not sure if I am placing it in the wrong place or if I'm losing it from trying so many things! haha

I have also tried the following which incorporates your expression:

1)

MinMax:

LOAD Min(MonthStart(MonthYear)) AS MinDate,

     Max(MonthStart(MonthYear)) AS MaxDate

RESIDENT [TABLE NAME];

LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

MasterCalendar:

LOAD Distinct

  Year(ReportDate) AS Year,

  Month(ReportDate) AS Month,

    Date(ReportDate,'MM-YYYY') AS MonthYear;

LOAD

  Date(MonthStart($(vMinDate) + RecNo() - 1)) AS ReportDate

AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

DROP TABLE MinMax;

Exit Script;

2)

LOAD [Table Name],

     Date(MonthStart(Num#([Date Field])), 'MM/YYYY') as MonthYear,

Year(num#(Date Field,'#')) as Year,

Month(num#(Date Field,'#')) as Month

Resident [Table Name];

DROP Table [Table Name];

and none of them worked or only partly worked. I realise I need to take a training on date functions though. The only place I can get your expression to work is as a master item in the UI. That allows me to view the information as DD/MM/YYYY then I need to put other expressions to view as MonthYear, Month, Year etc...

But is there away of avoiding changing script in the data load editor or having to make master items by addressing the issue in the preparatory stage before loading data? As per the screen shots I attached you can change field type from general to date and sometimes Qlik recognises it and changes everything to date format (and auto generates a master calender function for you), and other times it just gives you blank cells like in the second picture.

Best,

Mohammed

[#4]

Hi Mohammed,

could you provide us a sample of the result that you are getting with the above expressions?

I saw you used Date(ReportDate,'MM-YYYY') AS MonthYear, if you try with Date(ReportDate, 'DD/MM/YYYY'), do you still have errors?

Nicolò

[malradi88 · Accepted solution]

Hi Nicolo,

Thank you for your support. In the end this worked:

Temp:

Load num#([Date Field],'#') as NumericDate

RESIDENT [TABLE];

MinMax:

LOAD Min(NumericDate) AS MinDate,

   Max(NumericDate) AS MaxDate

RESIDENT Temp;

LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

MasterCalendar:

LOAD Distinct

text(num(MonthStart(Date($(vMinDate) + RecNo() - 1)))) AS [Date Field],

Year(Date($(vMinDate) + RecNo() - 1)) as [Year],

Month(Date($(vMinDate) + RecNo() - 1)) as [Month],

    ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter,

Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear]

AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

DROP TABLE MinMax;

DROP TABLE Temp;

[#6]

Hi Mohammed,

I'm glad you found a solution at the end

Best regards,

Nicolò