Faulty Sorting

I recently had a problem some straight table charts in a model that would allow sorting in only one direction. In other words, double clicking the header would sort by that header, but double clicking again would not reverse the sort direction. In fact, only the direction set in the Sort tab of the chart properties would work.

The chart had normal dimensions and expressions and no apparent reason for this behaviour.

The problem turned out to be that the first expression had been disabled. When I deleted this expression, sorting returned to the normal behaviour.

The lesson I learned here was that if I want to disable an expression during model development, demote it so that it is the last rather than the first expression in the chart.

Using QV 10 SR2

When Totals Don’t Work

Qlikview (QV) provides for three types of totals in straight tables and pivot tables:

• None
• The expression calculated at the total level
• Aggregate function (sum, average, minimum etc) of the table rows

These options cover most cases, but sometimes you have a table where none of these options is appropriate, or provides the correct result. As an example, consider a table of absolute variances, such as budget amount vs selling amount, on a table dimensioned by product class. The expression for the variance and % variance would be:

[AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount))

[%AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount)


These expressions yield different results for a different degree of dimensioning, so the sum of the absolute variances per ProductClass is not the same as the absolute variance at the total level. Therefore, the AbsoluteVariance column needs to be totalled using the Sum of rows option. For the %AbsoluteVariance, however, the expression at the total level does not work for the same reason, and the summing of the rows is arithmetically meaningless. In this example, a total is required for both of these columns.

So, how do we construct an expression that will produce the correct %AbsoluteVariance for each row and for the total?

We have the expression for the rows:

[%AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount)

For the total, we need an expression like:

[%AbsoluteVariance] = Sum(Rows of AbsoluteVariance) / Sum(BudgetAmount)

Assuming that ProductClass was the only dimension on the table, the Sum(Rows of AbsoluteVariance) can be calculated as follows:

Sum(Aggr(Fabs(Sum(BudgetAmount) – Sum(SellingAmount)), ProductClass))

So, how do we differentiate between the two expressions?

This is where the QV Dimensionality() function comes in. In our case, Dimensionality() will return zero when calculated on the total row, and greater than zero elsewhere. Check the Qlikview Reference Manual for more information on the function.

Our final expression becomes:

If(Dimensionality() = 0,
// total row expression
Sum(Aggr(Fabs(Sum(BudgetAmount) – Sum(SellingAmount)), ProductClass)) / Sum(BudgetAmount),
// other row expression
Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount))

QED

Unzip the download here and play around with a sample QVW model and the Excel source file.

Searching QlikCommunity

Many of you will know that the forums of QlikCommunity are extemely helpful if you have a QV problem.

Heres a useful tip that I picked up elsewhere:

You can use google to search QlikCommunity. For example, enter this as the Google search string:

Rolling 12 Months site:qlikview.com

Copying colours

Did you know that you can copy colours from one colour block to another in QV?

Qlikview colour block

Right click on the colour block for a context menu with the following options:

1. Copy - copy the colour information to the clipboard. This information includes the colour gradient information.
2. Paste All - paste the complete colour information from the clipboard.
   
3. Paste Base Color - paste the base colour only.
   
4. Paste Second Color - paste the second colour (if any).
   
5. Paste Gradients - paste the gradient information (type and shading style).

Design Mode

One of the criticisms of Qlikview that I have heard raised is that there is no design mode - in other words the same environment is used to design models and to use them.

However, QV does have a design mode - of sorts. I didn't know this until recently and stmbled across this indirectly in another blog (sorry, can't remember whose).

To activate design mode, turn on the design grid. On the menu click View | Design Grid or press Ctrl-G to toggle on and off.

OK, so what do I get in this design mode, apart from the grid of course? Well, now I have sizing/moving handles on sheet objects, and some additional context menu items (z-ordering sheet objects, column width aids, interactive sorting and indivdual cell properties)

I said "of sorts" above because it is not a pure design mode like other applications. You can still interact with the model as a normal user, mixed up with the design stuff.

Selection and month views

If you set up a simple month view chart such as a chart of the sum of transaction amounts by day, for example:

• using a dimension such as Day(TransDate)
• using an expression like Sum(TransAmount)

Now, if the user views this chart with no year and month selected, they will get the sum of amounts for that day of the month for all months loaded into the model. Not very useful.

Another approach is to make the chart display summed amounts for the current month. If a selection is made, then display summed amounts for the selected month/year.

This is how I do this:

1. Create TransYear and TransMonth fields on loading
2. Put listboxes with these two fields on the sheet
3. Build the chart
4. Filter the chart expression(s) with the following date filter (added filter highlighted):

Sum(If(TransDate >= MonthStart(Max(Total TransDate)) AND TransDate <= MonthEnd(Max(Total TransDate)), TransAmount)


5. Now, if nothing is selected, the TransDate range will be in the current month (or at least the last month in which a transaction was recorded - in most cases this will be the same date).

If you select a month only, then Max(TransDate) will be the month in the most recent year that contains data for that month. For example, in April: selecting Jan, Feb, Mar or Apr will display data for that month in the current year. Selecting any other month would display data for that month in the previous year.

If you select a year only, then Max(TransDate) will be the last day of that year with transactions. If that is the current year, then that will be today (or yesterday).

Any other selection (of TransDate, TransMonth, TransYear) will cause the month with of the last possible TransDate to be displayed.

You can, of course, do the filtering with a set expression if you prefer. I find this syntax more intuitive, but the final choice should depend on performance. Maybe you would like to convert this to a set expression in a comment below?

Tooltip for buttons

Did you know that QV models can display a tooltip for buttons?

Enter the tooltip text in the Help Text box on the Caption tab of the button properties. If the user hovers their mouse over the button, the text will pop up in tooltip.

This also works for text boxes.