In Qlikview charts (except Gauge charts and Pivot tables), you can use the Dimension Limits tab to control the number of dimension values you can see in a given chart.
In this post, I will demonstrate how setup a Qlikview Dimension Limit based on variable. The variable value is setup by an InputBox drop-down, then used to display qualifying values based on the top X percentage of the overall accumulating value of the expression.
Qlikview Help for the option Show only values that accumulate to:
When this option is selected, all rows up to the current row are accumulated, and the result is compared to the value set in the option. The relative to the total option enables a relative mode which is similar to the Relative option on the Expressions tab of the property dialog, and compares the accumulated values (based on first, largest or smallest values) to the overall total. The value may be entered as a calculated formula. Click on the … button to open the Edit Expression Dialog. Select Include Boundary Values to include the dimension value that contains the comparison value.
Note! Negative values will not be included when calculating the accumulated restriction sum. We recommend that you don’t use relative restrictions for fields that can contain negative values.
How to implement a Qlikview Dimension Limit based on Variable value:
Here are the steps to accomplish this task:
Step 1 – Create a variable and name it vDisplayPct
Then click on “Add”, type in vDisplayPct as Variable Name and click “OK”
Step 2 – Add an Input Box object to your Qlik app, then go into Properties
In the General Tab, select variable vDisplayPct as the Displayed Variable
In the Constraints tab, check the “Number Series” box and put in desired drop-down range (in Predefined Values section).
Also, select the “Predefined Values with Scroll” radio button option in the Value List options.
Step 3 – Add a Straight Table Chart object to your Qlik app, then go into Properties. In my example, I am setting up a chart to display the Sum of Fantasy Football Points for each Player (2014 season).
Step 4: Setup Dimension Limit
Go into the properties of the chart, and proceed to the Dimension Limits tab. Uncheck the “Show Others” box in the Options section.
Check the “Restrict which values are displayed using the first expression” box. Then select the option to “Show only values that accumulate to:” radio button option.
Here is where you will incorporate the Variable value that is selected by the user in the InputBox.
Click on the Expression Builder once you have selected “Show only values that accumulate to:” (directly under the radio button selection). Type in the text below:
Click OK, then go to the General Tab and input the following for the window title of the chart (just to add a little more dynamic display):
='Top '& vDisplayPct & ' % Fantasy Points'
Click OK again. Select the value of 15 in the InputBox and you will see your Chart dynamically update
In the above display, there are a total of 31,740.94 Fantasy Points for the 2014 season. The chart displays the players that make up the top 15% of that total. Doing the math:
- 15% of 31,740.94 equals 4,761.14
- The players Sum total of points equals 4,540.52
- The addition of the next player would exceed the top 15% of the total Fantasy Points so it would not qualify of display
If you change the InputBox selection to 5%
By using this technique, the user can now input a value to dynamically filter the chart. This is helpful to allow your users to have control over what they would like to see in the chart object, allowing them to achieve clear results for a given analysis. This solution provides a simple, clean method to accomplish this task.