Variables as Aggregators

Dashboards tend to be a compromise between space and information. Anything we can do to optimize our use of space will make our dashboards more useful and valuable. A good way to optimize your space is to use variables as your aggregators.

An example is the use of Sum and Average. Currently, if we want the user to be able to see both and save space, we’ll use a container or a button with conditional show. The problem with these methods is that you have to maintain two or more charts to keep them in sync.

You'll need four variables. Let's call them vSum, vAvg, vSumAvg and vSumAvg2. Create them by going to Settings > Document Properties... and clicking on the Variables tab. Below shows the areas where you'll be making changes:

vSum

Create the variable and type Sum in the Value expression box. Click Apply.

vAvg

Create the variable and type Avg in the Value expression box. Click Apply.

vSumAvg

Create the variable. Click the check box next to Listed Values. Type Total;Average in the Listed Values expression box. In the Input Constraints area, check Predefined Values Only. In the Value List area, check Predefined Values in Drop-down. Click Apply.


vSumAvg2


Create the variable and type =If(vSumAvg = 'Total', vSum, vAvg) in the Value expression box. It's important to include the '=' sign.

Next, create your chart. In this example, a simple bar chart.

The magic is in the expression. Instead of using something like sum([# of Buildings]), use:

     = $(vSumAvg2) ( [# of Buildings])

In past posts we discussed $ sign expansion. The $ sign tells Qlikview to insert the value of the variable as text. vSumAvg2 resolves to either Sum or Avg, depending on the value of vSumAvg. We'll set the value of vSumAvg with an input box.

Now, create an Input Box. Choose vSumAvg as your Displayed Variable.

The next task will be to position the Input box in a spot that makes it look like it's supposed to be there. My solution was to put it next to the chart title and change the Input Box properties to blend into the chart.

Use something like

     ='Property Information - ' & vSumAvg 

as your title. Then line up your Input Box to look like it's part of the title and put it on top of the vSumAvg portion of the title. When you change the variable, it looks like you're changing the title. And you are. It will export with the correct title and look right on the front end.

Trickery.