Posted on: 19th June 2020, 11:55 am
A dynamic table shows dimensions and measures based on the user’s selection rather than a static table with predefined fields. The user may select dimensions and measures of their choice and the table will adjust accordingly. A feature like this can be useful when the number of columns and rows increases in a table and you don’t want the abundance of fields to compromise the visualisation and overwhelm users with noise. This selective view, simplifies the presentation of the data and allows all users to get at the information they are interested in seeing, rather than showing them additional data that isn’t important to them but is for other users.
Using the Pivot Table chart and conditional columns
The native pivot table is a powerful chart that allows you to analyse multiple dimensions and measures at once by grouping your data, displaying sub totals, expanding and collapsing rows and columns. But even the pivot table may get a bit overwhelmed when the number of fields increases and does not give you those quick answers anymore.
Using the dynamic nature of the pivot table can help you overcome the challenge of multiple dimensions and measures as your data dimensions/metrics grow. With the functionality of conditional columns, also available in the straight table, in combination with a field/metric selection mechanism, you can add and remove columns of your choice instantly.
It’s worth noting there are various methods to achieve a dynamic table. We typically recommend one of two approaches depending on the requirements. The first is to create a data island with the fields/measure in the load script, this benefits from being able to select multiple fields/metrics to display. The second is to use the Variable input extension that Qlik supports, the benefit of this approach is that it doesn’t show your selected choice in the Selections toolbar by default and you don’t have a data island forming part of your data model. For this post, I will focus on the first approach, and here are the 3 steps required:
1. Create an inline table in the load script that will serve as your selections table.
Identify all the selectable fields that the user should be able to add and remove from the pivot table. List the selectable fields in an inline table in the load script like the example below. The inline table should act as a data island and should not be associated with the other tables in the data model.
LOAD * INLINE [
2. Making use of the conditional show or hide column.
Add the pivot table chart on a sheet and add all selectable fields to the column and row segments of the table as desired. Under each field, in the properties panel, use this code in the “Show column if” expression editor (fx):
if(SubStringCount(GetFieldSelections([Dim Selections]),’Transaction ID’)=1,1,0)
To clarify what this conditional statement does, it shows the column if the value “Transaction ID” has been selected in the “Dim Selections” field.
To be more specific, the GetFieldSelections function returns all the selected values in the field “Dim Selections” in our recently created inline table. The SubStringCount function counts how many times our value “Transaction ID” occurs among the selected values. Finally, the IF statement checks if “Transaction ID” occurs among the selections and returns a true or false (1 or 0) if the condition is fulfilled.
3. Add a filter pane for selections.
Lastly, add a filter pane to the sheet with the field “Dim Selections” where the user can select the available listed fields. When one or more selections has been made in the filter, the fields will populate the table consequently.
Optional step: if no selections has been made, the pivot table will return the message “The calculation condition is not fulfilled” for every field that this applies to. To tidy this up in the user interface and direct the user to make selections, you can make use of the calculation condition option for the entire table and a displayed message if no selections has been made.
Go to the properties panel of the table and the section “Add-ons” followed by the subsection “Data handling”. In the Calculation condition expression write this code:
and in the Displayed message expression write something like this:
=’Select fields in the Dimension Selections filter pane’
The displayed message will appear in the center of the pivot table if no selections has been made in “Dim Selections” filter.
Why not use Master Items to drag and drop?
To drag and drop predefined master dimensions and master measures to build your customised pivot table can serve as an alternative solution to the dynamic table. After all, the master items is by default a functionality in Qlik Sense that allows the user to explore and reuse ready-made dimensions and measures.
However, it requires the user to edit the application and create a new personal sheet. This personal sheet may be shared with other users but the same process will be required for the next user who wants to customise the table again.
The dynamic table is therefore a more accessible and general option that only requires the user to make selections in the application.
By Mats Severin
Very useful post Mats - thanks! Just a note to say I'd tweak three things about this... 1. Since GetFieldSelections can sometimes return things like "ALL" or "7 of 15" or even "NOT Val1", I use concat to make sure I get all the values. 2. I also give a delimiter to make sure we don't match an accidental substring (If one dimension is called "Material" and another "Material Name") then you could end up showing Material Name when you select Material. 3. Depending on the desired behaviour, I check if there are any selections on the Dim Selections field - in your example it will show all the fields if nothing is selected which is often not what you want. So in the "Show Column If" property, instead of: if(SubStringCount(GetFieldSelections([Dim Selections]),’Transaction ID’)=1,1,0) I'd have: =if(GetSelectedCount([Dim Selections])>0 and substringcount('|'&concat([Dim Selections],'|')&'|','|Transaction ID|')=1,1,0)
Alex Walker, 9th July 2020
Cool! As always! You could serialize app and upload it to the GitHub with gitoqlik. And then paste link to your Github repository. So, your example app will be available to community and every Qlik user could reuse your template on their app. Recipe is here - https://docs.gitoqlik.com/code-sharing/how-to-share-your-load-script-subroutines-with-the-qlik-community
Alex Polorotov, 19th June 2020