How to create a PIVOT table in standard Qlik Sense

    Everyone claims that you can't create a pivot table in standard Qlik Sense yet. But the truth is you can. You can do it without creating redundant data in your data model and it is very efficient too - calculation wise. Using an ancient technique known nowadays as Cross Table Join you can create a pivot in a "standard table" (or Straight Table as it is called it in QlikView). I used this technique almost 25 years ago with SQL Server 1.1 on OS/2 doing the visualization in Excel 3.0 which had no pivoting at that point - neither did SQL Server. The generic technique has been used perhaps decades before that - I am not joking.

     

    Joe Celko has documented the approach in his book "Joe Celko's Analytics and OLAP in SQL" from 2006 - Chapter 2 pages 7 to 11.

     

    It is MUCH simpler to do it in Qlik Sense since you get the help of the Associative Data Model. What you need is a helper table which is similar to an identity matrix. Using the natural join you get by a common key-field in Qlik and the natural grouping function and a Sum(keyfield*VALUE) you will get the expected result.

     

    Sure this leaves much to be desired when it comes to interactivity but for now it is quite helpful to be able to do a pivot anyway. You don't have to wait for a future version of Qlik Sense to get basic pivot functionality.

     

    First the result:

    Pivot1.PNG

     

    In this standard table the MEASURES are like this:

     

    =Sum(Jan*Value)   for January (Jan is the name of a field in the helper table)

    =Sum(Feb*Value)   for February

    ....

    =Sum([Year Total]*Value) for the year total

     

    The Year Total for all months is also calculated with the helper table. I have attached the full sample Qlik Sense Application

    for you to inspect so you can use the technique for your own benefit.

     

     

    Then the raw data table:

    Pivot2.PNG

     

    If anyone would like to have a more through guide to this technique I would be happy to provide it here on the Qlik Community. But I leave it as this for tonight as I think many of you might be able to make use of it with this short introduction and example.

     

    Happy pivoting !

     

     

    Petter Skjolden

    Senior BI and DWH Consultant

    RAV Norge AS