Adam Cooke

I currently work as a consultant for Qlik, a software company doing some interesting stuff in business intelligence. Check it out Qlik.com. I write this blog to showcase some technical bits that I hope will be useful to others.
adamcooke@webofwork.com

Tags

Handle Qlik Dates inside an extension using Moment.js

Webofwork > Ideas  > Handle Qlik Dates inside an extension using Moment.js

Handle Qlik Dates inside an extension using Moment.js

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:

//This will handle any Qlik Date serial number
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.

user-gravatar
Adam Cooke

I currently work as a consultant for Qlik, a software company doing some interesting stuff in business intelligence. Check it out Qlik.com. I write this blog to showcase some technical bits that I hope will be useful to others.

3 Comments
  • Bill Markham
    Reply
    Posted at 8:54 am, 29th June 2016

    I was faffing around with dates in a QlikSense mashup a couple of weeks ago and I needed a couple of buttons to select previous and next date from that currently selected. It took me a while to suss I did not need to do any date conversions.

    The mashup was already using an extension exploiting datepicker.js downloaded from Branch, so I could already select a date and store it in a variable.

    With a boostrap button, using the QlikSense API’s, I retrieved the date from the variable, added / subtracted one to /from it and used that to make the selection & update the variable..

    Initially I did it converting dates from Qlik format to Javascript format, manipulating them and converting back to Qlik format. Until I remembered a Qlik date is simply a number and one can simply just process it as a number.

    This will only work for simple use cases like mine. I’ll definitely have a look at your suggestion as I can see it would be useful for more complex use case scenarios.

    Many thanks for sharing.

  • afa
    Reply
    Posted at 2:04 am, 10th September 2017

    “In recent years has been very popular gold and gold necklace, Black Spinel Crystals Cubic Zirconia stonesgold a bit like the feeling of riches, platinum with more people, the color is really beautiful, especially the skin of the white girls, gold with a more touching.
    Gold jewelry sales in China, cheap pandora braceletsa steady rise, indicating that we are in line with the international market. Gold jewelry because of its lighter weight, the price is slightly lower, exquisite craft, cheap pandora Spacers
    fashionable and more and more like everyone’s favorite. The prize money is made up of 75% of 18K gold and other alloys.”

Post a Comment

Comment
Name
Email
Website