Recently I was trying to use dates as one of the dimensions inside an extension.

Qlik can deal with lots of types of dates, when they are passed to the extension they come through in both a text format in the way they were formatted within Qlik using the date time functions and the Qlik Serial Number. This is handy, but since I wanted the extension to work in a variety of regions and date formats, I found myself wanting to use the Qlik serial Date number.

To clarify, all dates in qlik are stored as a  date-time serial number that equals the number of days since December 30, 1899. The integer value represents the day and the fractional value represents the time of the day. An example of the integer would be around 41000 for a date in 2012.

I used Moment.js to easily convert this serial number to any format of date I would like to use inside the extension. This seems to give me an easy way to handle ANY Qlik date time format and then quickly convert it to a standardized format, whether I want to use UTC or ISO or anything custom date format I like based on the moment.js documentation.

Great!

Heres the code:



define(["./properties", "qlik", "jquery", "client.utils/routing", "./js/moment"],
  function (Props, qlik, $, Routing, moment) {
    'use strict';
    var app = qlik.currApp(this);
  }(); 

return {
  // new object properties 
  initialProperties: {
    version: 1.02,
    qHyperCubeDef: {
      qDimensions: [],
      qMeasures: [],
      qDebugMode: true,
      qInitialDataFetch: [{
        qWidth: 9,
        qHeight: 200
      }]
    }
  },
  definition: Props,
  paint: function ($element, layout) {
    var qData = layout.qHyperCube.qDataPages[0].qMatrix;

  //Loop through initial data array 
  var arrayLength = qData.length; for (var i = 0; i < arrayLength; i++) {
      console.log(qData[i][0].qText);
      var storystartdate = moment('1899-12-30').add((qData[i][1].qNum * 86400000) + 1, 'milliseconds');
      var start_date_formatted = {
        day: storystartdate.format('D'),
        hour: storystartdate.format('H'),
        minute: storystartdate.format('m'),
        month: storystartdate.format('M'),
        second: storystartdate.format('s'),
        year: storystartdate.format('YYYY')
      };

The key part is this:


var storystartdate = moment('1899-12-30').add((qData[i][1].qNum * 86400000) + 1, 'milliseconds');

This converts the Qlik Serial Number into milliseconds and adds it to the base Serial Date that Qlik/Excel uses.

I’m interested to know of any other ways people have done this task.