Dual values for “self-sorting”

Sometimes you need a field that has a unusual sort order. For example, you might have an a range field that contains values <0, 0-10, 10-100, >100. When you use this field as a dimension in a table or chart, you will want to sort this in the order above. In many cases, this can be done by using the “Load Order” option, but there are cases where the natural load order may not work (calculated fields, for example).

The load order is also of no use if you plan to use the field in a rank expression.

In these case, one option is to create dual values for the field. A dual value contains a text representation and numeric value. Dates are numeric, containing the formatted date and numeric date representation. They display the formatted date, but sort using the numeric value. You can also perform arithmetic on the numeric value. But did you know that you can create your own, custom, dual values?

To do this, use the Dual() function. This is an example of a calculated field using Dual:

LOAD

...

If(IsNull(PaymentAmount) Or PaymentAmount = 0, Dual('None', 0),

If(PaymentAmount > 0.9 * ExpectedInstallment Or IsExempted, Dual('>90%', 90),

If(PaymentAmount > 0.5 * ExpectedInstallment, Dual('50 - 90%', 50),

If(PaymentAmount > 0 Or InSuspense, Dual('0% - 50%', 1),

Dual('Reversal', -1))))) As PayGroup,

…

FROM …

In this example, the load order does not reflect the required sort order, but values of Paygroup will sort according to the numeric value:

Reversal, None, 0% - 50%, 50% - 90%, >90%

Just remember to ensure that the “Numeric Value” sort option is selected when you need this sort order.

Howto: Create a Waterfall Chart

A waterfall chart is a type of cumulative bar chart, where each bar begins at the previous cumulative total and has a length equal to the value at that dimension (positive or negative). Something like this:


So, how do we go about this.

CREATE BAR CHART
Create a bar chart with the dimension you require for the X axis, and the following expressions:

• Total (this is the value for the final, total bar - green in the example above)
  
• Value (this is the value for the blue and red bars - rename as appropriate)
  
• Cum (this is a hidden value which tracks the cumulative value)
  

For example, let's have a simple data set with fields Dim1 and Value1. There is a dummy Total with Dim1 = Total, Value1 = 0.

Use the expressions:

• Total: If(Dim1 = 'Total', Sum(Total Value1))
• Value: Sum(Value1)
• Cum: RangeSum(Above(Column(3)), Column(2))

Set Cum to Invisible on the Expressions tab of the chart properties.

MAKE THE WATERFALL

The trick now is to use the bar offset on the Value expression and use this expression:

=If(IsNull(Above(Column(3))), 0, Above(Column(3)))+if(Column(2)<0,Column(2),0)

Click the + sign next to the expression name in the Expressions tab to see the Bar Offset.

That should do it. You can download a demo here : Waterfall demo

To colour the negative values red, set the Background Color for Value to the expression:

=If(Column(2)<0, RGB(200,110,130))

Have fun!

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

Understanding Duals

QV stores all numeric data (numbers, dates, time, intervals, etc) in what is called “dual” format. Understanding dual format may help in overcoming some model pitfalls and may assist in solving some problems.

In brief, a dual format value comprises and numeric value, and a text representation. These may be standard number or date formats, they may be built in series (eg months, days of the week) or any customised series (more on this in a later post). The text representation is the equivalent of a format, but it can be more than that.

To illustrate, the number 40658 could represent:

• The value 40658. Depending on the format this could be displayed as 40,658 or 40658.00 etc.
• The date 18 April 2011.
• A product (or customer or branch or region etc) code 0040658
• etc

The number 0.65 could also represent:

• A percentage - 65%
• The time of day 3:36 pm (0.65 of a day of 24 hours from midnight to midnight)
• An interval of 15 hours 36 minutes (0.65 of 24 hours)
• Etc

The format can be created implicitly by QV. During a load, it will infer the format from the source data. If a certain column in a CSV data source contains numbers:

8.35000
9.15000
3.62333

Then QV will infer a format of 0.00000 for that field. Certain QV date and time functions also create the output in the default time and date format for your model.

Taking control – formatting commands

By default, QV will display the text portion of the dual on text boxes, captions, dimension/expression labels and the like, and will use the numeric portion for arithmetic expressions.

You can also manually control this with the Num() and Text() functions. For example, if a field “TimeElapsed” represents an interval with a value of 0.25 (6 hours), then Num(TimeElapsed) will return 0.25 and Text(TimeElapsed) will return 06:00:00 (assuming your default interval format is hh:mm:ss).

Some functions (like Min, Max, Avg) only return the numeric part of the expression, others return a dual value - check the return type in the auto-completion prompts or the documentation.

The formatting commands (Num(), Money(), Date(), Time(), TimeStamp(), and Interval()) allow you to control the text representation. It is worth pointing out that they have no effect on the numeric portion.

For example, Date(40651.65, ‘YYYY/MM/DD’) will display 2011/04/18, but the numeric value is still 40651.65. In other words, the Date function does NOT truncate the fractional part of the value, it simply does not display it. (To remove the time from date/time values, use Floor()).

That provides a very simple conceptual overview of dual values and formatting in Qlikview and I hope it helps with your understanding of dual values.

I will address some of “dual” issues in a little more depth in further posts on this site in the near future. I value your feedback and any topic suggestions or questions.

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.

A Novel Bookmark Display

This post arose after a client asked for a view of document bookmarks that was similar to a horizontally arranged list box selector. The QV model had a number of states which could be selected using these bookmarks. He wanted his staff to be able to see the selected. The solution looked something like this:

I achieved this as follows:


Step 1: Create the document bookmarks in the normal manner.

Step 2: Open the bookmarks dialog (Bookmarks | More) to get the bookmark Ids for each bookmark. Then uncheck the checkboxes in the “+” column.

Step 3: Create an inline table in the load script with the bookmark names and IDs – something like this:

LOAD * INLINE
[
   BMName, BMId
   Stock on hand, BM32
   Reclaim pending, BM30
   Legal hold, BM26
   Write offs, BM33
   Phyical stock, BM29
   Saleable stock, BM31
];

Step 4: Create a list box containing BMName, and arrange horizontally. You can also sort the list box by load order, if appropriate.

Step 5: Add the BMName selection to each bookmark, by selecting the bookmark (from a bookmark control, or the menu. Then select the corresponding BMName value. Finally, click Bookmarks | Replace bookmark to update the bookmark with the BMName selection. I used an open Current Selections box to verify the correct behaviour.

Step 6: (Almost there!) Go to Settings | Document Properties | Triggers and add an On Select trigger on the field BMName. Add a Clear All action, followed by an Apply Bookmark action. For the Bookmark ID for the latter, enter “=BMId” (without the quotes!).

That’s it. Now selecting a value in the “selector bar” for the bookmarks will apply the bookmark and show the currently applied bookmark. Clicking the selected value will clear the bookmark (ie normal list box behaviour).

If you find this useful, or would have any comments on this technique, please post a comment.