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.
- 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>
- 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 } - 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 } - 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": "," } } } - 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": "," } } } - 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": "," } } } - Define title
The title is defined in the options.
{ "showTitles": true, "title": "Golf statistics" }
Result
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.
- 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" } }, - 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
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.
- 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))" }, {} ] }, - 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