Category Archives: Visualization

Alt States Merged Selections Tip

Summary: I suggest a simpler syntax for merging selections from multiple states. 

You may be familiar with the “Product Grouping” example from the “What’s new in QlikView 11.qvw” sample.  It’s a great  beginner demo of using Alternate States for comparative analysis.

In this visualization, a user can select two sets of values from the [Product Sub Group] field. The two different sets are represented by two states, [Group 1] and [Group 2].  The blue [Group 1] bar is plotted on the bar chart using this expression:

sum({[Group 1]<Region = $::Region, [Sales Rep] = $::[Sales Rep], Path = $::Path, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} Sales)

[Group 1]  as the Set Identifier indicates we want to start with the selections in [Group 1]. We then modify, or add, selections from the default state by referencing each field with the “$::fieldname” syntax.

The green [Group 2] bar is created with a similar expression, the only difference being [Group 2] as the Set Identifier.

The “$::fieldname” syntax works, but it forces us to list every field. Listing every field can get difficult. Is there an easier, more generic method?  Yes, if we redefine the problem as:  All selections from the Default state except for [Product Sub Group].

sum({[Group 1] * $<[Product Sub Group]=>} Sales)

Breaking the Set Expression down:

[Group 1]    // Group 1 selections
*    // Intersected with
$<[Product Sub Group]=>   // All selections from Default except [Product Sub Group]

I’m not suggesting  the sample is wrong. The “$::” syntax is useful to know and is required when you want to reference only specific fields.  I’m posting this alternative because I see people copying this more complex $::  syntax when the simpler syntax would suit their application.

-Rob

Join me at an upcoming Masters Summit for Qlik event in Johannesburg (6-8 Sept) or Austin (10-12 Oct).  Oleg Troyansky’s Set Analysis and Advanced Aggregation session provides  more useful tips and advanced techniques in using Alternate States.

 

 

QV12 Timestamp Parsing

Have you noticed something new in QlikView12 and Qlik Sense timestamp parsing? UTC timestamps are automatically understood.

(Note: the output displayed below utilizes the US Date format set in the script as:  SET TimestampFormat=’M/D/YYYY h:mm:ss[.fff] TT’;)

For example, the expression:

=Timestamp('20160504T142523.487-0500')

returns:

5/4/2016 7:25:23 PM

That is, the UTC offset of “-0500” is detected and the returned value is the UTC time, not the local time of 2:25:23 PM.

I can’t find anything in the help beyond an example  for Timestamp# that demonstrates this but provides no detail.

This parsing functionality is particularly useful now that the QlikView Server logfiles use the UTC format for times.

I’m not sure yet if I like the automatic conversion to UTC time.  For example, apps like the QlikView Governance Dashboard now report Session Start or Event times in UTC time, not local time.

It’s nice that the “T” character is understood. If you want local time, it’s easy enough to drop the offset (“-0500”) as

=Timestamp(left('20160504T142523.487-0500', 19))

which returns

5/4/2016 2:25:23 PM

-Rob

 

Scaling Numbers and DSE Tips

Summary: I demonstrate a simple reusable expression to auto scale numbers in QlikView. This leads to an exploration of some of the finer details of dollar sign expansion.

A QVW example to accompany this post is available for download at http://qlikviewcookbook.com/wp-content/uploads/2016/05/ScalingNumbers.qvw.

The QlikView auto-scaling feature selects an appropriate unit – billion, million, thousands — based on the magnitude of the  Y-axis values.  It’s a nice feature  available in Line and Bar charts.  How can we create the same functionality in Text Objects or Straight Tables?

It’s easy enough to use an if() function that tests the magnitude, does any necessary division, and formats appropriately. For example:

if(Sum(Sales)>1E6, num(Sum(Sales)/1E6,'$#,##0.000M')
 ,if(Sum(Sales)>1E3, num(Sum(Sales)/1E3,'$#,##0.000K')
 ,num(Sum(Sales),'$#,##0')
 ))

(The 1E6 is an easier way to write 1000000).

To avoid repeated coding of “Sum(Sales)” I create a reusable variable with parameters in the script like this:

SET vScaleNumber=if($1>1E6, num($1/1E6,'$#,##0.000M')
 ,if($1>1E3, num($1/1E3,'$#,##0.000K')
 ,num($1,'$#,##0')
 ));

Now I can use the variable vScaleNumber in a Text Object as:

=$(vScaleNumber(Sum(Sales)))

The string “Sum(Sales)” will get substituted in  every occurrence of “$1”.  I ‘ll get an appropriately formatted number like:

If I use “$(vScaleNumber(Sum(Sales)))” in a Straight Table expression without label, hovering over the column heading will show me the full substitution in  a tooltip.

I  can see that the “$1” substitution occurs before the expression is evaluated, and the substituted expression looks like:

if(Sum(Sales)>1E6, num(Sum(Sales)/1E6,'$#,##0.000M')
 ,if(Sum(Sales)>1E3, num(Sum(Sales)/1E3,'$#,##0.000K')
 ,num(Sum(Sales),'$#,##0')
 ))

I’ve avoided re-typing “Sum(Sales)”. But I may have a concern about the performance implications of repeated execution of “Sum(Sales)”.  And what about more complex expressions such as “Round(Sum(Sales),10)”?  The comma in that expression will break the syntax as variable parameters always treat commas as parameter separators.

I can fix the comma/performance problem by using Dollar Sign Expansion (DSE) with an “=”.  The “=” will cause the expression to evaluate and pass the numerical result to vScaleNumber.

=$(vScaleNumber($(=round(Sum(Sales),10))))

Checking the expansion in a Straight Table shows:

if(1783150>1E6, num(1783150/1E6,'$#,##0.000M')
,if(1783150>1E3, num(1783150/1E3,'$#,##0.000K')
,num(1783150,'$#,##0')
))

I  see the value of “round(Sum(Sales),10)” has been calculated as “1783150”,  yielding an efficient and syntactically correct expression.

Next  I’ll add a Dimension to the Straight Table.  The row results are incorrect!

The “=” in the DSE caused the Sum expression  to be evaluated only once for the entire chart, yielding the same value for every row.  How to fix?

I will calculate the sum() expression in a n Expression n column, and then hide this column on the Presentation tab. I can then refer to the hidden column:

=$(vScaleNumber(Column(1)))

Once again, the expansion yields an efficient and syntactically correct expression.

if(Column(1)>1E6, num(Column(1)/1E6,'$#,##0.000M')
 ,if(Column(1)>1E3, num(Column(1)/1E3,'$#,##0.000K')
 ,num(Column(1),'$#,##0')
 ))

I started this post by demonstrating a simple expression to format scaled numbers. It’s a function I frequently use.

For more on DSE, see Henric’s post at https://community.qlik.com/blogs/qlikviewdesignblog/2013/11/18/dollar-expansions

A QVW example to accompany this post is available for download at http://qlikviewcookbook.com/wp-content/uploads/2016/05/ScalingNumbers.qvw.

-Rob

Yoke Dashboard

I was chatting with a colleague recently about trends in BI and I brought up what I call the “commoditization of metrics” .  Google Analytics is an early example of this — your data crunched and delivered at the KPI level.

I recently ran across a great example of the commodity metrics idea:  Yoke.io.

Yoke let’s you build your own dashboard using metrics gleaned from cloud services such as Gmail, Twitter and Github. Here’s a portion of my Yoke dashboard. It’s all built with a few clicks and no coding.

Yoke Image

Give Yoke.io a try, it’s free!

-Rob

Join me at the upcoming “Masters Summit for Qlik” in Milan on 5-7 April. In addition to learning about all things Qlik, we’ll be talking about trends in Dashboarding and BI. 

Scoping Selections with Aggr()

Summary: Selections can be made in Calculated Dimensions, although the result may not always be what is expected or desired.   The Aggr() function can be used to control what field(s) get selected.

The technique discussed in this post applies to both QlikView and Qlik Sense.  The screenshots shown are from QlikView.  Some of the visuals are a bit different in Qlik Sense, but the idea and expressions demonstrated are the same.

A downloadable example to accompany this post is available here.

Consider a listbox created with an <Expression> value of:

=Customer & ' -- ' & Country

A listbox constructed this way is useful for providing additional context or an  additional search.

Selections made in that listbox will make underlying selections in both Customer and Country.

The user is probably  expecting a selection in Customer only.  To limit the selection to Customer, add an Aggr() function to the expression:

=aggr(
 Customer & ' -- ' & Country
 ,Customer)

Only the Customer field is listed in the Aggr() dimension, so selections will be made only in Customer.

A side effect of adding Aggr() is that gray (unassociated) rows no longer display.  We can fix that with a bit of Set Analysis.

=aggr(
 only({1<Customer={"*"}>} Customer & ' -- ' & Country)
 ,Customer)

Now the listbox looks and behaves as expected.

 

Another place you may need Aggr() to control selection intent is chart Calculated Dimensions.

Hovering over a Salesrep value in the chart below gives a contextual popup that identifies Manager and Hire Date associated with the Rep.

The column was created as a Calculated Dimension:

=SalesPerson
& chr(10) & 'Reports to ' & [Sales Manager]
& chr(10) & 'Hire Date ' & date(HireDate,'YYYY-MMM-DD')

Clicking Michelle in the chart correctly selects her name as SalesPerson, but makes unexpected selections in HireDate and SalesManager.

I’m going to say that the dimension is “improperly scoped” and correct it by adding Aggr() to the Calculated Dimension.

=Aggr(
 SalesPerson
 & chr(10) & 'Reports to ' & [Sales Manager]
 & chr(10) & 'Hire Date ' & date(HireDate,'YYYY-MMM-DD')
 ,SalesPerson)

Selections will now be correctly limited to the “SalesPerson” field.

 

We’ve seen that Aggr() can narrow selections. We can widen selections as well.  This listbox will make selections in Customer, Country, SalesPerson and Year, even though only Customer is displayed in the listbox.

=aggr(
 only({1}
 Customer
 )
 ,Customer, Country, SalesPerson, Year)

 

We don’t have to include the display field in the selections.  In what I’ll call a  “backdoor associative search” , this expression will display Customer, but selects only the OrderID values associated with the Customer.

=aggr(
only({1}Customer )
,OrderID)

It’s usually a best practice to pre-create Calculated Dimensions in the script, when possible, for performance reasons. Returning to our first example, we might create a new field in the script as:

Customer & ' -- ' & Country AS CustomerAndCountry

We can use the new field as a display value, but we want selections to be made in Customer.

=aggr(
 only({1}  CustomerAndCountry)
 ,Customer)

 

As a last example,  we can create  “bookmark” like alternatives; either new fields linked in the data model or advanced search at run time.

Here I’ve linked a hidden field named “Bookmark” into specific OrderIDs in the script.  I want selections to be reflected in the OrderID field.

=aggr(only({1}Bookmark), Bookmark,OrderID)

Here is an advanced search that presents a listbox of Customers who have placed at least one order with a value >50K.

=aggr(
only({1<OrderID={"=sum({1}OrderAmount)>50000"}>}Customer )
,Customer)

Aggr() can be a “heavy resource consumer” and has the potential to slow down your application. Use only when required and avoid using or benchmark the impact in large applications.  Calculated Dimensions can also be a source of slow performance, precalculate fields in the script when possible.

Download the  example qvw for this post .

-Rob

 

 

A Better Show Frequency

I like using “Show Frequency” in a Listbox, but the feature suffers two drawbacks.

  1. If the Field is in a Dimension table, the frequency — usually 1 — is not particularly useful to the user.
  2. Excluded (gray) rows show a blank frequency value.

As an alternative to “Show Frequency”, we can use the Expression pane of the Listbox to provide a more meaningful number, such as count of orders.

num(
 count(DISTINCT OrderID)
,'#,##0')

Listbox Expressions don’t provide for a heading.   We can fake one by adding it the Listbox Title with enough spaces to align the heading.

The excluded values show zero. We can fix that by adding a Set to the expression. The Set should honor selections in other fields such as Product or Year, so we only need to ignore the selection in this field.

num(
 count({<Customer=>} DISTINCT OrderID)
,'#,##0')

Optionally, we can use the same expression as a Sort Expression if we want to sort by Order count.

There you have it.   A more useful Frequency for the QlikView Listbox.

The Qlik Sense Filter Pane does not provide an Expression property, but you can achieve a similar result in Qlik Sense by creating the Field as an expression,

aggr( 
    only({<Customer=>}Customer) 
    & repeat(chr(160),8)
    &num(
    count({<Customer=>}DISTINCT OrderID)
    ,'#,##0')
,Customer)

 

 

The “repeat(chr(160),8)” is a trick to insert 8 “non-breaking” spaces. A string of multiple regular ‘  ‘ spaces will display as only a single space when displayed in the browser.

You may want to place the numbers on the left side to make the numbers clearer.

And yes, you could dynamically calculate the number of spaces required to get multiple digits right-aligned.

Using  aggr() to create a Filter Box is a much “heavier calculation” than just using the Field. If you have a large application, test this technique for performance before deciding to use it there.

-Rob

Pivot Table Grids

Using dimensions on the X and Y axis and plotting measures at the intersection is a useful visualization. The out of the box solution in QlikView is the Grid chart.

The grid chart does have limited options for representing data.

The Pivot Table can be a more robust alternative.  A Pivot Table grid (Crosstable) is created by dragging one of the dimensions to the horizontal position.

 

Now we can represent the measures as a heatmap by coloring the background of the cells.  This is frequently a more effective visualization than circles,  .

We can also display numbers in the cells.

 

We can display multiple expressions per dimension.

 

Here I show both the number and a linear gauge allowing for clear comparisons between Regions. The user can swap the dimensions if desired.

 

You can mix colors and numbers to show interesting patterns. Here’s a map of average Sunrise/Sunset/Daylight for my home town.

Maybe it’s not a design winner, but I find it interesting.

 

We can blank some cells in the grid to make meaningful shapes, in this case a rough outline of the United States.

All of the examples shown can be downloaded here.

A few styling tips:

  • Style format “Clean” is a good starting point for managing border lines and spacing.
  • “Custom Format Cell” Border before/after (double line) can be used to create white space between rows as shown in the Minimum Wage example above.  Also, setting Text Size to other than the default 100% can be useful.

Do you have any examples of Pivot Grids to share?

-Rob

 

Bar Chart Viz Tidbits in Version 12

It’s been quite a while since my last post. Summer vacations, Masters Summits in New York and Copenhagen, a Luminary meetup in Lund. Lots of new input for blogging.

I’ve been testing the QV Version 12 Beta and am pleased to see  a few simple but useful enhancements in bar charts.

  •  Multi-line horizontal legends on x-axis. Works in both vertical and horizontal orientation.
  • “Plot Values Inside Segments” now honors scaling/symbols property from the Number pane,
  •  New Presentation option: “Still Show Total on Top” when plotting Values in Segments.

These enhancements allow you to easily create charts like this example, even with values in  thousands or millions.

 

 

You will need to create your own line breaks in the dimension. There is no auto wrap yet.  Create a calculated dimension that replaces blanks with the newline char:

=replace(ProductName,' ', chr(10))

Another QV12 plus — those annoying false syntax errors in the expression editor have been cleaned up and the expression editor is my friend again.

I’m looking forward to discovering more new features in QV12 as my testing continues.

-Rob

Using SR11 “SilentErrorInChart” Switch

Last month I wrote about error handling changes in Qlikview SR11. Today I want to relate my experience in using the new “SilentErrorInChart” switch during development.

Let’s start with an excerpt from the SR11 Release Notes:

As a result of fixing bug 69228 “Syntax checker not working”, syntax error messages in object expressions are now returned to objects instead of returned as NULL. This may result in an object which rendered in SR10 or earlier, displaying an error message in SR11 (”Error in expression”).

This feature changes what you see — if a chart column has an error. Prior to QV11,  the chart would render, but values in the error column would display a null (“-“):

With SR11, the default behavior is to not render the chart and instead display a chart level error message:

This is useful. It clearly raises the flag that one of this chart’s expressions is returning an error. But which expression? You will need to go through the expressions one by one, and if there is more than one column in error, you will repeat the exercise.

Can I return to the pre-SR11 behavior? Where the chart renders what columns it can, so I can identify what columns are returning errors? Yes! There is a setting for that.

The setting is not (yet) configurable through the User Preferences Dialog. So we need to use the backdoor “easter egg” to modify the setting. To reach the easter egg settings dialog, select “Help. About QlikView”  and then Right-Click on the QV bullseye logo in the lower left of the dialog box.

 

In the Settings list, scroll down and select (left click) “SilentErrorInChart” to display or modify the current setting.

A Value of “0” for this setting means to use the new SR11 behavior. That is, any column in error will cause the entire chart to not render.

A Value of “1” will return to the pre-SR11 behavior. QV will render what columns it can, and display “-” for those columns with errors.

To modify the setting, overtype the Value, press the Set button followed by the Close button. There is no need to exit QV to recognize a change for this setting. However, the chart must be “re-rendered” to utilize the setting. Easiest method I have found is to edit and save (“OK”) the chart Properties.

I’m finding that during most development, I set “SilentErrorInChart” to “1”. I want to identify columns in error as I create them.

In my final pre-production check, I’m finding that changing “SilentErrorInChart” to “0” is a useful quality control check to dramatically surface any chart expression problems.

-Rob

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