Skip to main content Skip to complementary content
Qlik Resources

Creating pivot tables

This section describes how to create pivot tables with the Visualization API and qlik-visual.

Creating a basic pivot table

In this example we create a basic pivot table, containing two dimensions (Year and Quarter) and three measures. The measures have basic number formatting applied and a title is added to the pivot table.

  1. Create the chart

    Create the container for the chart. The visualization type is pivot-table.

    Visualization API

    app.visualization.create(
  'pivot-table',
  [],
  {}
)

    qlik-visual

    <qlik-visual appid="Tutorial-Golf.qvf"
  type="pivot-table"
  cols='[]'
  options='{}' >
</qlik-visual>
  2. Define the first dimension

    Define the dimension Year as a column. Note that the dimension does not include null values: "qNullSuppression": true.

    { "qDef": { "qFieldDefs": [ "Date.autoCalendar.Year" ], "qFieldLabels": [ "Year" ] }, "qNullSuppression": true }
  3. Define the second dimension

    Define the dimension Quarter as a column. Note that the dimension does not include null values: "qNullSuppression": true.

    { "qDef": { "qFieldDefs": [ "Date.autoCalendar.Year" ], "qFieldLabels": [ "Year" ] }, "qNullSuppression": true }, { "qDef": { "qFieldDefs": [ "Date.autoCalendar.Quarter" ], "qFieldLabels": [ "Quarter" ] }, "qNullSuppression": true }
  4. Define first measure

    Define the measure as a column and label it FIR%. Apply custom number formatting since the measure should be displayed in percent.

    { "qDef": { "qLabel": "FIR%", "qDef": "Avg(FwHit)", "qNumFormat": { "qType": "F", "qnDec": 2, "qUseThou": 0, "qFmt": "0.0%", "qDec": ".", "qThou": "," } } }
  5. Define second measure

    Define the measure as a column and label it GIR%. Apply custom number formatting since the measure should be displayed in percent.

    { "qDef": { "qLabel": "FIR%", "qDef": "Avg(FwHit)", "qNumFormat": { "qType": "F", "qnDec": 2, "qUseThou": 0, "qFmt": "0.0%", "qDec": ".", "qThou": "," } } }, { "qDef": { "qLabel": "GIR%", "qDef": "Avg(GIR)", "qNumFormat": { "qType": "F", "qnDec": 2, "qUseThou": 0, "qFmt": "0.0%", "qDec": ".", "qThou": "," } } }
  6. Define third measure

    Define the measure as a column and label it Putt avg. Apply custom number formatting since the measure should be displayed with two decimals.

    { "qDef": { "qLabel": "FIR%", "qDef": "Avg(FwHit)", "qNumFormat": { "qType": "F", "qnDec": 2, "qUseThou": 0, "qFmt": "0.0%", "qDec": ".", "qThou": "," } } }, { "qDef": { "qLabel": "GIR%", "qDef": "Avg(GIR)", "qNumFormat": { "qType": "F", "qnDec": 2, "qUseThou": 0, "qFmt": "0.0%", "qDec": ".", "qThou": "," } } }, { "qDef": { "qLabel": "Putt avg", "qDef": "Avg(Putts)", "qNumFormat": { "qType": "F", "qnDec": 2, "qUseThou": 0, "qFmt": "#,##0.00", "qDec": ".", "qThou": "," } } }
  7. Define title

    The title is defined in the options.

    { "showTitles": true, "title": "Golf statistics" }

Result

Example pivot table

The image below shows the same example with year 2013 expanded

Example pivot table with a measure category expanded

Code examples

Adding totals row

In this example we add a totals row to the pivot table. This is enable in the qOtherTotalSpec object of the first dimension.

  1. Add totals row

    Add the totals row in the column definition of the first dimension (Year): "qOtherTotalSpec": { "qTotalMode": "TOTAL_EXPR" }. This means that the total of the dimension values is returned.

    { "qDef": { "qFieldDefs": [ "Date.autoCalendar.Year" ], "qFieldLabels": [ "Year" ] }, "qNullSuppression": true, "qOtherTotalSpec": { "qTotalMode": "TOTAL_EXPR" } },
  2. Add label

    Then add a label for the totals row: "qTotalLabel": { "qv": "Totals" }.

    { "qDef": { "qFieldDefs": [ "Date.autoCalendar.Year" ], "qFieldLabels": [ "Year" ] }, "qNullSuppression": true, "qOtherTotalSpec": { "qTotalMode": "TOTAL_EXPR" }, "qTotalLabel": { "qv": "Totals" } },

Result

Example pivot table with totals row

Code examples

Using background color and text color

In this example we use expressions to color the cell backgrounds and the text in our pivot table. This enables us to use expressions to define both the colors used and the conditional values upon which the colors are applied in a visualization.

Background colors and text colors are defined in the qAttributeExpressions object inside the applicable measure definition in the columns. The qAttributeExpressions object consists of two arrays, where the first array is for defining the background color and the second array is for defining the text color.

  1. Background color for first measure

    We add background color to the first measure: "qAttributeExpressions": [ { "qExpression": "If(Avg(FwHit)>0.667, RGB(145, 194, 106))" }, {} ]. This will color the cell green for values that are above 66.7%.

    { "qDef": { "qLabel": "FIR%", "qDef": "Avg(FwHit)", "qNumFormat": { "qType": "F", "qnDec": 2, "qUseThou": 0, "qFmt": "0.0%", "qDec": ".", "qThou": "," } }, "qAttributeExpressions": [ { "qExpression": "If(Avg(FwHit)>0.667, RGB(145, 194, 106))" }, {} ] },
  2. Text color for second measure

    We then add text color to the second measure: "qAttributeExpressions": [ {}, { "qExpression": "If(Avg(GIR)<0.1,RGB(255, 115, 115))" } ]. This will color the text red for values that are below 10%

    { "qDef": { "qLabel": "GIR%", "qDef": "Avg(GIR)", "qNumFormat": { "qType": "F", "qnDec": 2, "qUseThou": 0, "qFmt": "0.0%", "qDec": ".", "qThou": "," } }, "qAttributeExpressions": [ {}, { "qExpression": "If(Avg(GIR)<0.1,RGB(255, 115, 115))" } ] },

Result

Example pivot table with background and text color modifiers

Code examples

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here