I was recently poking around in the QlikView 11 demo “Social Media Data Analysis“.  This app was first brought to my attention in the excellent blog post “Use of a Silent Legend” by  Jennell McIntire.  I like how color is used to quickly create linkage between elements on the sheet. It’s one of my favorite tips.

Looking at the mechanics of the color & object construction, I thought that while fine for a demo, there were a few things I would do differently. One item in particular — the Company listbox — stood out because of a new technique available in QV11.

The listbox contains a color chip for each Company that maps to the color used in the charts. Very effective.

The color chips are constructed as 6 individual Text Objects. This is maybe the only possible way to get that particular effect in a V10 listbox.

However, that means that I, the challenged artist, have to line those boxes up and keep them aligned. It also means that I have to be very sensitive to the contents of the listbox changing.

V11 introduced Listbox Expressions which opens up a new option.  I took the individual color boxes and put them in  jpg files. I bundle loaded (embed in the qvw) the images with this script:

Images:
bundle info load * inline [
Company, image
Company A,image\Square_Orange.jpg
Company B,image\Square_Blue.jpg
Company C,image\Square_Green.jpg
Company D,image\Square_Brown.jpg
Company E,image\Square_Red.jpg
My Company,image\Square_Purple.jpg
];

Next step was to add this Expression to the listbox:

='qmem://Company/' & Company

and select “Image” for the Expression Representation.

And now my color chips are an integral part of the listbox. Also, the chips now disappear for excluded values which is consistent with the visual behavior of the rest of the sheet.

I won’t be surprised if commenters come up with an even better way 🙂

-Rob

We usually think of charts as having both Dimensions and Expressions. But that’s not always the case. Sometimes it’s useful to have only one or the other.

A Straight Table with Dimensions and no Expressions is very similar to a Table Box. Unlike a Table Box, you can add Expressions later if needed and you also get improved styling options.

The trick to getting rows to display when there are no Expressions is to uncheck “Suppress Zero-Values” on the chart properties Presentation pane.

A Dimensions only Pivot Table is a useful way to display a hierarchy. This can look very nice with the Indent style.

Charts may also contain Expressions only — no Dimensions. Here’s a Straight Table with two expressions and no Dimensions.

We get a single row table — the sum of all data — along with a redundant Total line. By turning off the Totals and doing some styling, we can build a nice KPI display in a single table without needing to line up multiple text objects.

We get the full power of Straight table layout and styling, number formats, visual cues etc. I flipped the columns to the horizontal by checking “Horizontal” on the Presentation pane.

Bar Charts may also be created without Dimensions. Each Expression is plotted as a separate bar, in either the grouped or stacked style.

When an Expression only Bar Chart is stacked and oriented horizontal, it can be an  interesting alternative to the Linear Gauge chart.

In QV number formats we can use specify different patterns for positive and negative values. The two patterns are separated by a semicolon like this:  positive;negative

#,##0;-#,##0

Did you know a third pattern can be specified? It’s used for zero values.

#,##0;-#,##0;–

Using the above format zero displays as a double hyphen “–“.  Entering nothing after the second semicolon will result in zero being displayed as a blank.

The Qlikview Reference Guide appears to make no mention of the second and third formats. However, the formats look like Excel custom formats. Using that documentation, you’ll see that text may be used to create interesting formats like:

+#,##0.00% Increase;-#,##0.00% Decrease;No Change

Which when combined with Visual Cues can produce chart output like:

-Rob

Chart properties provide the capability to suppress the display or calculation of a column. In this post we’ll explore the difference between those two options and look at some use cases.

The Presentation Show/Hide Column feature was introduced in QV10. The Dimension/Expression Conditional is new in QV11.

The Presentation pane of a Straight Table Chart provides an option to show or hide each individual column. The decision to Show/Hide may also be specified as a conditional. If the condition evaluates to true, the column will be displayed.

Hidden columns are calculated and are “present” as data in the chart. The resulting column  is not visible in the rendered chart. Let’s look at a use case for this feature.

Straight Tables expressions may reference other columns in the table as data items.

The Net Sales expression above uses column labels:

=[Gross Sales] – [Sales Tax] – Cost

We can hide the intermediate columns and still use them as data to calculate the “Net Sales” column. This is a useful technique for building up a complex calculation.

The final chart displays only the “Net Sales” column.

The same hidden column technique is available in Bar and Line Charts as well. However, in this case we hide the column by unchecking the Bar (or Line) Display Option  on the Expression pane.

Let’s turn our attention to hiding Dimensions. In the example below, if we want one row for each Order we must include a unique field like OrderId in the Dimensions.

What if we don’t want to display the OrderId, but still want one row per Order? If we remove OrderId from Dimensions the table rolls up to a row for each Description value. That is not what we want.

We can get the desired result by leaving OrderId in the Dimensions and hiding the column on the Presentation pane.

A hidden dimension column participates in defining the rows of a chart. This is a useful feature, although it’s utility is not always obvious.

Qlikview Version 11 introduced  the Dimension/Expression Conditional property. This is enabled by checking Conditional (on the Dimensions or Expressions pane) and supplying an expression to be evaluated. If the expression evaluates to True, the column will be calculated. If False, the column will not be calculated. If it is not calculated, it cannot be referenced by another expression.

A common use for the Conditional property is to toggle columns on or off in a wide text chart.

The button toggles the variable “vShowDetail” which is tested in the Expression conditional:

Another common use case is the “create your own chart” or “dynamic reporting” where users are allowed to pick Dimensions and Expressions from a list. This property — Dimension/Expression Conditional — is the correct way to implement this. If you instead implement the conditional in the Presentation pane, resources will be wasted calculating values that will not be displayed or referenced.

I find an interesting application for conditional Dimensions in Pivot Tables. I sometimes use buttons or other conditions to enable dimension levels. This presents the same output as Pivot table Expand/Collapse commands but without the clutter of the +/– buttons.

A QVW containing all the examples shown here may be downloaded from Qlikview Cookbook: Tutorial – About Column Visibility.

-Rob

A Customer showed me this stacked bar chart and asked how to “make the Goals a different color”. What he really wanted was to differentiate the Goal stacks from the Actual stacks — but still be able to associate the Channels (Consumer, Online,…).

Channel values are expected to change over time, so any hard-coding of Channel to color would require maintainence. Here’s the Background Color expression I suggested:

if(Type=’Goal’
,argb(96,255,255,255) bitand color(FieldIndex(‘Channel’,Channel))
,color(FieldIndex(‘Channel’,Channel))
) 

If that makes perfect sense to you, read no further. Otherwise let me break the expression down.

What we are doing in the expression is setting the Alpha value to “96” when “Type=Goal”. “Type=Actual” will retain the Alpha default of “255”.

Color codes in QV are made up of four numbers — Alpha, Red, Green, Blue — values which we can set in a color dialog or a color function. Alpha indicates the amount of transparency, ranging from 0 (fully transparent) to 255 (fully opaque). Colors functions without an explicit Alpha value like “RGB(0,128,0)” default to Alpha=255.

The function “Color(n)” returns the color code for the “Nth” position in the chart color palette.

FieldIndex(‘Field‘, Value) returns the position of  Value in Field (by load order).  This will generate a number for each distinct Channel value, regardless of how many Channel values are loaded. We are effectively assigning persistent colors because FieldIndex() is not affected by selections.

1. We use color(FieldIndex(‘Channel’,Channel)) to select color n from the palette.

2. If  Type=Goal  we use the boolean bitand operator to set the Alpha value for the selected color to “96” without modifying the RGB values. Adjust this 96 value for an effect of your liking.

-Rob

I’m always tickled to learn something new in QV, especially when it’s simple and elegant. swuehl, a prolific and respected contributor to QlikCommunity, answered this Community post with a brilliant solution.

The problem was how to calculate a Project (the Dimension) duration using dates from two different rows in the source table. I would have automatically reached for a script solution, Generic Load. But what if you don’t want to modify the script or don’t want the flattened model produced by Generic Load?

Swuehl’s solution computed the value in the chart using Set Analysis to fetch the needed values from the appropriate row.

only({<progress = {deployed}>} entry_date ) 
   – only({<progress = {started}>} entry_date)

The cleverness lies in using the only() aggregation function. Set Analysis can only be used in aggregation functions, so he used only() to enable specifying the Set.

I like it.

-Rob