Pivot tables in QlikView are great.
Sometimes though, you want to enable users to change the dimensions of the pivot without having make changes to the Chart.
One way to this would be to have 6 different charts that they could choose from and use the auto-minimize feature on the charts to manage the displays. It just seemed to be too much to maintain, so I found an alternative.
The issues to solve are:
1. Do not display the chart unless all three dimensions are selected
2. An item can only be selected for one dimension.
3. The display names for the dimension headings are different from the underlying Field Names
I’m going to tackle item 2 first… an item can be used only once. Nothing really elegant about that as it turns out – it is simply an inline table with three columns called Field1, Field2, Field3. Create a line for each of the combinations you want to allow, so you will have 6 lines total. If you were to look at them as numbers, then the combinations would be 1,2,3 and 1,3,2 and 2, 1, 3 and 2, 3, 1 and 3, 1, 2 and 3, 2, 1. In my case it looked like this:
LOAD * INLINE [
Field1, Field2, Field3
OriginalMarketingStatus, MediaType, RequestType
OriginalMarketingStatus, RequestType, MediaType
MediaType, OriginalMarketingStatus, RequestType
MediaType, RequestType, OriginalMarketingStatus
RequestType, MediaType, OriginalMarketingStatus
RequestType, OriginalMarketingStatus, MediaType
To enable the user to make their selections, I used a MultiBox with Dimension1, Dimension2, Dimension3 as the available fields. Of course you can substitute individual list boxes if you prefer.
Next up – I want to translate the dimensions into headings. This is another inline table / data island. In mine, I wanted to pretty things up and shorten one to an code, so it looked like this
LOAD * INLINE [
MediaType, Media Type
RequestType, Request Type
The next piece you need is to have variables for your dimensions – I just called them D1, D2, and D3 and set up formulas to set their values.
D1 = GetFieldSelections(Field1), D2=GetFieldSelections(Field2), D3=GetFieldSelections(Field3)
Now for the pivot table
For your dimensions, use $(D1), $(D2), and $(D3)
For labels, use the calculation =FieldValue(‘DisplayName’, FieldIndex(‘FieldName’, D1) )
Repeat for D2, D3
This uses the Dimension Labels table as a dictionary by finding the correct row using the field name, and then referencing the corresponding value.
And of course – the final requirement – only show it when its ready.
Navigate to the layout tab and set it to show conditionally on the condition below – which is to select one field from each of the three dimension choices.
= (GetSelectedCount(Field1) = 1) & (GetSelectedCount(Field2) = 1) & (GetSelectedCount(Field3) = 1)
In mine, I added one more touch – and that is a dynamic window title.
= ‘By ‘ & FieldValue(‘DisplayName’, FieldIndex(‘FieldName’, D1) ) & ‘, ‘ & FieldValue(‘DisplayName’, FieldIndex(‘FieldName’, D2)) & ‘, ‘ & FieldValue(‘DisplayName’, FieldIndex(‘FieldName’, D3) )
The overall effect is very cool, and as a developer – means only having to maintain one chart instead of 6.
It takes a little bit of playing around with it to get everything just right… but it is worth it. You will probably also want to have a text object on the bottom layer where the chart should display to communicate with the user that they need to select all the dimensions to show the chart.