Category Archives: Visualization

SR11 Error Handling Changes

SR11 introduces two changes to error handling that you should be aware of.

The first change is that syntax errors marked with the “red squiggle” in the expression editor will always generate a status message of “Errors in expression”. This was not the case prior to SR11.

In SR10 and prior there could be a difference of opinion between the red squiggles and the status message. Valid syntax such as this–

SR10

in SR10 could be marked with squiggles but the “Expression OK” message would reassure us that this was just a syntax checker bug.

In SR11 the status message will always be “Errors in expression” when a red squiggle appears — even when the syntax is valid.

SR11

This applies as well to the very common squiggles that appear when using set expressions with variables. Again, this is a functional expression:

SR11

So now the squiggles and the status message are in alignment. I’m not sure this is a good thing. We  can no longer say “Ignore the squiggles. If it says ‘Expression OK’ then it’s fine”. If this change sticks then we will have to get more aggressive about reporting and fixing syntax checker bugs.

The second Error Handling change you should be aware of is documented in the SR11 release notes. Charts that contain invalid expressions will now fail to render. In SR10 they assumed some sort of default and the chart was rendered. This new behavior applies to chart expressions,  dimension attributes and expression attributes such as Text Color. If any of those expressions are in error, you will get various error messages in the chart frame. I don’t understand  the pattern of the messages yet.

The good news is that this behavior is not implemented in Server until you explicitly turn it on with a Server Settings.ini switch. By default, Server charts will continue to render as they did in SR10. When you turn the setting on, the Server will log the “bad charts” to the event log. Unfortunately, the logging does not happen until you activate the new behavior. This means your logs and your users see  the problem at the same time. Pity we can’t log before we turn the option on.

So what’s the combination of these two changes? If the syntax checker declares a perfectly good expression to be bad will the chart render? Yes, it will render. The syntax checker’s opinion and the actual evaluation of the expression are not connected.

I’m still trying to understand how these changes will affect my customers and I am holding off recommending SR11 until I can experience a few upgrades. If you have some experience good or bad with these changes please leave a comment.

-Rob

Share

Cell Popups in Straight Table

Last week my Masters Summit colleague Kevin McCann showed me a cool technique for generating popups (hover text) in individual cells of a Straight or Pivot Table. This can be useful for providing contextual info like order line detail:

 Or contact info when hovering over a name:

The Dimensions are created as multi-line Calculated Dimensions. Sorting and selections work as expected. If not done correctly, selections will be made in the referenced fields of the popup as well. The trick to avoiding the secondary selections is the use of aggr(). Customer is defined as:

[qlikview type=”exp”]=Customer & chr(10)
& aggr( EmailAddress & chr(10)
& Phone
,Customer )[/qlikview]

A complete example with instructions can be downloaded here: Qlikview Cookbook: Cell Popups In Straight Table

-Rob

Share

Info on INFO or What is the “i” for?

You may have noticed that “i” icon appearing in a QlikView listbox and wondered “why is it there?” and “what’s it good for?”. Or you may have seen script “INFO LOAD…”and wondered what it did.

The “i” icon and INFO LOAD are both associated with the QV Info feature which has some interesting and useful applications.

Some INFO is created for you right out of the box with no effort required. Create a listbox for Field $Table.  (If $Table is not available in your list of fields,  Right-Click, Select Fields and check Show System Fields).

Select one $Table name The “i” appears in the caption when one value is selected.

Clicking the i will display information about the table source. If the table was created from a database, the SQL statement is shown.

If the table was created from an excel or other local text file, the external file will be opened!

Internally, INFO is a two column table that associates a Field value with an external image or data file. The $Table example shown above was created automatically. You can create your own INFO tables using the INFO LOAD script statement. The following  script connects OrderNumbers with the associated Invoice PDF file.

[qlikview type=”qvs”]
InvoicePDF:
INFO LOAD * Inline [
OrderNumber, InvoiceFile
SO67707, PDF\Invoice_SO67707.pdf
SO67710, PDF\Invoice_SO67710.pdf

[/qlikview]

Clicking the i opens the associated PDF file in Adobe Reader or the default application for *.PDF on your machine.  If the file is not physically available, the the text string “PDF\Invoice_SO67707.pdf” will display in a popup.

For Access Point usage, note that opening external files in this way does not work from the Ajax client. It does work with the IE Plugin or Desktop client. (The workaround is to use Open URL Action).

Note that tables created using INFO LOAD do not appear in the Table Viewer.

A common use of INFO is image files. The  optional prefix, “BUNDLE”, may be used to include the external file contents in the qvw itself. This is done for portability, as the files need not be available at runtime (works with all clients).

[qlikview type=”qvs”]
T1:
BUNDLE INFO LOAD * INLINE [
Fruit, val
Cherry, Image\Cherry.jpg
Banana, Image\Banana.jpg
Apple, Image\Apple.jpg
Lemon, Image\Lemon.jpg
];
[/qlikview]

Note that the name of the second field (“val” in this case) does not matter as it is never referenced in expressions or the UI.

We can use the Fruit INFO in a number of ways.  First we can use the field Fruit in a listbox and select “Info as Image” Representation on the Presentation tab:

The listbox will show the associated images instead of the text. Sort and Search in this listbox will use the Fruit text values, as we might expect.

Once again, when a single Fruit is selected we get our friend i. Clicking i will display the image in it’s original size in a moveable window. The window remains until the user closes it.  You can pull up multiple images side by side and arrange them. Imagine pulling up multiple insurance claim or property photos.

Another way to utilize INFO data is in an expression using the Info(fieldname) .  In the Straight Table below I’ve used the expression Info(Fruit) to create the second column. The Representation for that Expression has been set to “Image” on the Expressions pane.

The  Info() function produces results only when there is one value for the given context.  In a Text Object,  Info(Fruit) would produce a null value as long as there is more than one Fruit possible.  In this way, Info() works similar to the Only() function. Unlike Only(), Info() does not accept a Set expression to select a specific value. That kind of conditional logic requires using the qmem syntax instead of Info(). (For more on qmem, see the Help for BUNDLE ).

So what happens if we try to BUNDLE LOAD non-media files like excel or PDF? Disappointment I’m afraid.  They will load. Your qvw will get larger. However the Info display won’t work correctly. You can of course leave off the BUNDLE prefix for those file types and make sure they are available at runtime to the user.

Info can also be invoked as an Object Action.  The Action is “Show Information” and it can be found in the External Action Type.

Have fun and let me know how you use INFO.

-Rob

PS  I hear the early bird discount for the May SF Masters Summit has been extended. See the news here

Share

Listbox Expressions

Prior to the introduction of Listbox Expressions in QV11, the only additional data you could put in a listbox was Frequency.  The Show Frequency option has some shortcomings:

  • The meaning of frequency is dependent on the data model. For a customer name in an orders table, frequency may correctly reflect the order count.  If the customer name is moved to a dimension table, the  frequency is “1” .
  • Frequency values show only for possible rows — green and white. Excluded gray rows show nothing in the Frequency column.
  • Show Frequency is not available for Key fields.

Listbox Expressions solve these problems and more. Here are are a few tips on using Listbox Expressions.

On the Expressions tab of a listbox properties,  you can add one or more expression columns, as you would in a chart. The “Dimension” will be the listbox field.

While the dialog may be similar to a chart, there are a few features that are not available, notably:

  • Number format — you must do any desired formatting in the expression.
  • Column labels — the workaround is to include the “label” text in the Caption.
  • Totals.

If you want your Listbox sorted by the expression value, repeat the expression in the Sort pane “Expression” property. There is no need to include the “Num()” formatting in the Sort Expression, but no harm in leaving it in either.

Now we have a nicely sorted listbox that provides context about “customer size”.

When selections are made, excluded data shows zero, probably not what we want. Fix that up by adding a Set  to the expression.

sum({1}LineSalesAmount)

What if we want to the Sales expression to reflect other selections, like Product, but still want to see all Customers? Simple, add a set modifier to ignore Customer.

sum({<Customer=>}LineSalesAmount)

Like a chart, we can add additional expressions such as order count,  days since last order, or account rep name.  We also have the full range of expression representations; Image, Gauge, Mini Chart etc. We can even put pictures in the listbox.

There is no visible vertical line separating  columns, but the columns may be resized by dragging at the invisible  boundaries.

If you get frustrated trying to make a listbox look like a chart, take a step back. Listbox expressions are meant to guide the user in making selections, not present a finished analysis.

-Rob

Share

Listbox Color Chips

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

Share

Qvc Gets Colorful

Qlikview allows customization of the basic eighteen color palette at the Document or User level through property dialogs. For color control beyond the palette,  many developers utilize variables.

The set of colors and variables I use for a document or project I refer to as a “color theme”.  I’ve built up several themes over the years as well as generously borrowed theme ideas from others.

Wait! Isn’t there an single ideal theme we should all use? No. I find a regular need to update or craft new themes in response to client requirements, the latest understanding of best practices, and frankly, fashion. 

In V9 of the Qlikview Components (Qvc) scripting library I’ve added a color theming routine.  The SUB Qvc.ColorTheme(themeFile) uses an external file to define color variables. The variables are then referenced by chart objects to implement the theme.

Implementing a color theme with Qvc requires two actions; assigning colors to the variables and assigning the variables to sheet objects.

Qvv.ColorTheme takes it’s input from an xlsx or xlsm file that contains at least these two columns:

ColorVariable — A variable name. Any variable name matching the pattern Qvc.Color.v.* will be kept by the routine. Other variable names may be used as intermediate names to compute final values.

ColorValue — a valid Qlikview script color function such as rgb(0,0,0) or white().

A sample theme is provided with the Qvc distribution in etc\Colors\ColorSample1.xlsm. This sample file also offers the option to use the Excel color picker to assign a color.

In the sample file you’ll see:

Specific colors defined early in the file that are referenced later:

“Logical” color variables that define “good” and “bad” things. 

“Object Attributes” that define items like the chart 18 color palette.

After including the qvc.qvs runtime we can add the script statement

CALL Qvc.ColorTheme('ColorSample1.xlsm')

and then reload. Great! Now our document contains a bunch of Qvc.Color.v.* variables with proper color values. How do we tell our object color properties to use those variables? Do we have to type them in each object? No.

Also included with the Qvc distribution is etc\QvcColor.qvt, a QV theme file that assigns the Qvc.Color.* variable names to object definition. You apply this qvt theme to your Document as you would any other qvt theme, using the “Layout, Apply Theme” button. Apply Theme is available at the Document level (Settings, Document Properties) or at the individual object level on the layout tab.

After pressing “Apply Theme”, select and apply the etc\QvcColors.qvt file. After Apply, you’ll see that color properties in the object (or all objects if done in Document Settings) reference the Qvc.Color.* properties.

Changes to the theme file will be reflected in the object colors after reload.

You may also assign the QvcColor.qvt theme as the default document theme for new objects in “Settings, Document Properties, Presentation”. This is particularly useful if you are starting a new document.

If you develop a color theme you find useful, please let me know on the QVC User Forum and I’ll include your theme as a sample in a future release.

-Rob

I’m now teaching a 3 hour on-line course on using the QVC library at http://q-on.bi/. The next course is scheduled for 7 Nov. See http://q-on.bi/courses/qlikview-components-scripting-library/ for details.

Share

Expressionless and Dimensionless Charts

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.

Free your mind from the idea that a chart always has Dimensions and Expressions and have some fun.
-Rob
Share

The Third Format

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

Share

Using Column Visibility

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

Share

Performance Tip – Using Dual() and Chart Visual Cues

I recently diagnosed a slow Straight Table chart. The chart contained 100K+ rows. One column contained a complex expression that returned a Y/N string flag for the column. Something like:

=if(complex expression, ‘Y’, ‘N’)

They also wanted to set the background color of the cell, green for Y, red for N. So the Expression Background Color property repeated the same complex expression to assign a color:

=if(complex expression, green(), red())

I surmised the expression was being calculated twice for each row. I changed  the main expression to set a Dual().

=if(complex expression, dual(‘Y’,1), dual(‘N’,0))

The chart cell still displays the Y/N text. But now I could use 1 and 0 values on the Visual Cues pane and eliminate the Background Color expression entirely. Much faster!

-Rob

Share