I just read an excellent book by Miguel Garcia and Barry Harmsen – “QlikView 11 for Developers.” I highly recommend it for beginning and intermediate QlikView developers. I even learned a few things myself! Like the pick(dimensionality(…), …) trick.
QlikView 11 for Developers at PacktPub
Miguel and Barry are two of my favorite bloggers by the way.
Miguel: iQlik – Everything QlikView
Barry: QlikFix.com
I recently had my first opportunity to use Alternate States on a real project. As awesome as I think Alternate States are, I just haven’t had a chance to implement them before now.
As I was developing the expressions for my charts, I came across a predicament. And after some hand-wringing and head-scratching I realized it was a result of how I’ve been treating the set identifier, $.
As a reminder the set identifier $ is the default set. It represents the entire data model PLUS all user selections. Insofar as it is the default, it doesn’t need to be explicitly included in the expression. Meaning, when the $ is omitted in a set analysis expression, QlikView defaults to the default set. In other words,
sum({ $ } Sales) is equivalent to: sum(Sales)
just as
sum({ $ <Year = {2010}> } Sales) is equivalent to: sum({ <Year = {2010}> } Sales)
(notice the dollar-sign is absent in the 2nd expression)
Now, although the $ is not required, I have diligently included it in any and all expressions that use set analysis, rather than letting QlikView default it. My reasoning on this was to prefer the explicit over the implicit, i.e. leaving nothing to chance and more importantly, improving code understandability. And I’ve coached my students and clients to do the same.
“Always include the dollar-sign.”
B. Lay, said to dozens of students
…turns out that wasn’t such great advice!!
So here comes the rub …
Without alternate states, the situation is simple: there is one and only one “default” state ($); namely the state of all user selections. But with alternate states defined, the concept of a “default” is no longer simple.
Instead of a single “default” state there are now:
Additionally, any expression in any object can either inherit the state of the object or be controlled via set analysis. And this is the key point: sum({ $ } Sales) and sum(Sales) now have potentially different meanings.
By hard-coding the $ in the set, we are explicitly choosing the good old-fashioned default state – all user selections. This ignores the alternate states. In fact, even if the object is assigned to an alternate state, by placing the $ in the set expression we are ignoring that alternate.
Contrast this to omitting the dollar-sign: sum(Sales) In this case we are accepting whatever state the object is in. That could be the default set $ … or it could be an alternate state, if indeed the object is assigned to an alternate state.
By omitting the dollar-sign we are allowing the set expression to “inherit” the object’s state.
Back to my issue (it’s all about me!) … I had created two identical charts, one in State A and the other in State B. And two groups of list boxes, one State A and the other in State B. Each chart had several expressions and included set analysis (for other calculation reasons). Here are the details (note I’m obscuring the client’s actual field names):
sum({$ <CurrYTD = {Y}>} Sales)sum({$ <CurrYTD = {Y}>} COGS)sum({$ <CurrYTD = {Y}>} Units)sum({$ <CurrYTD = {Y}>} #OrderCounter)
sum({$ <CurrYTD = {Y}>} Sales)sum({$ <CurrYTD = {Y}>} COGS)sum({$ <CurrYTD = {Y}>} Units)sum({$ <CurrYTD = {Y}>} #OrderCounter)
So this is following my old-fashioned approach of always including the set identifier $.
Hence, to my chagrin, when I clicked on values in my State A and State B list boxes, nothing changed. Again, because I had overwrote the object state by using a $ in my set analysis expressions. The fix for this was quite easy; simply removing the $ from the expressions.
sum({ <CurrYTD = {Y}>} Sales)sum({ <CurrYTD = {Y}>} COGS)sum({ <CurrYTD = {Y}>} Units)sum({ <CurrYTD = {Y}>} #OrderCounter)
sum({ <CurrYTD = {Y}>} Sales)sum({ <CurrYTD = {Y}>} COGS)sum({ <CurrYTD = {Y}>} Units)sum({ <CurrYTD = {Y}>} #OrderCounter)
Well, despite the resiliency of old habits 
I will now be omitting the $ in my set expressions.
Keep on Qlikin’
Bill
Here’s a nifty little trick I discovered by accident when creating INLINE tables…
Typically when I’m creating an INLINE table it’s for one of two reasons:
The technique I’m going to demonstrate is relevant to usage #1 above. In fact, it’s only useful if I’m going to base my INLINE table (in part) on data that already exist in my data model.
Consider this specific example:
What’s the easiest way to “append” the Region onto each Country record …. ???
In the past, when faced with the use case as described above, I would do the following:
BUT, there is a simpler way to achieve this, if you take note of a curious little “Tool” menu inside the Load INLINE table wizard…
Make sure the source field already exists in the data model. In our example it’s the Country field.
Go into the Script Editor (ctrl-E) and go to the Inline table wizard:
Insert > Load Statement > Load Inline
Check out the Tools menu inside the Inline Data Wizard window
Click on it and select the Document Data… option
Locate the source field in the list of Available Fields. You will have an option to select All Values (this is what I always end up using) or take advantage of the associative model if selections have already been made in the dashboard.
The wizard then populates the values from the source field into the first column of the table
The second (and subsequent) columns can now be manually populated. Which in our use case is adding the Region {EMEA, APAC, Americas}
Clean it up and reload. And the final result should look like this:
I hope you find a chance to use this shortcut in your future projects.
Keep on Qlikin’
Bill
If you haven’t seen it yet, there’s a nifty feature in the QlikView version 10 to pull metadata from the repository into a QVW.
The metadata are related to applications (QVW documents) in AccessPoint and the server objects that users can create. Specifically:
These metadata are not available in the Ops Monitor app (as far as I know), so this new feature is more than just a convenience.
Adding these fields to the load script is super-simple, thanks to a new option in the datasource selection drop-down menu – QvsAdminDataProvider.dll (32)
I made a couple of small tweaks to the script for convenience:
Rather than expressing the document size (DocumentList.FileSize) in bytes, I converted it to kilobytes using this code:
round(num(replace(FileSize,'.',''), '#') / 1024, 1) AS [File Size (kB)]
And I plucked the username out of the CollaborationMetadata.OwnerName:
subField(CollaborationOwner, '\', 2) AS OwnerName
Now reload the script! Your data model should look like this:
The simplest use case is to create table boxes and a few selectors. The Document List and Collaboration Objects are well-suited for tabular display. Here I’ve done a little renaming and formatting:
Now we can start having fun! With these new metadata available for collaboration objects, we can measure aspects of our QlikView program such as:
Here are just a few visualizations to serve as examples:
(this would be more meaningful with more than one user!! )
Hope these examples get you thinking about ways to incorporate these metadata into the monitoring and improvement of your QlikView program.
Keep on Qlikin’
Bill
Sometimes the simplest tasks are most challenging. Take for example the need to express a single, scalar number, such as the measurement of a KPI. One number, no dimensions, no time trend…simple, solitary…scary.
The trick is to NOT to overdo it. Don’t try to force a chart or speedometer or hygrometer or any other overblown visualization to do this job. In fact, the simplest way to express this result is probably just to state it, i.e. literally display the value as text.
Let’s consider an example from a call center. A key metric for the call center is call resolution time. This particular call center has a target resolution of five minutes. Here are some poor ways to visualize this metric (peruse any of Stephen Few’s writings if you’re not sure why):
If I had to choose one of these representations, the thermometer (far left) is the least offensive, but it consumes far too much space and the added context of seeing the level of “mercury” in the 100% gauge doesn’t really add much value.
Abandoning any of these visualizations, I opt instead for a simple text box:
To add some context to the metric’s value, we can utilize the the Text and the Caption areas of the text box.
By taking advantage of dynamic expressions in both the text and caption areas, you can build elaborate displays that combine both text and numerics. Be sure to spend some time on formatting the numbers. Functions like round(), num(), date() and the concatenation operator & (ampersand) are essentials.
Here is the code snippet for the caption:
=round(sum({$<[Affects SLA?] = {"-1"}>} [My Metric]), 0.001) * 100 & ' %'
And the text code:
='... of calls were resolved within ' & chr(10) & $(varCallResolutionTarget) &' min.'
where I have attempted to variable-ize all “moving pieces.”
The final result should look something like this:
I’ve coined this type of text box as a “factoid.” (“Unifact” sounds more representative, but a little too nerdy ). Maybe QlikView will object-ize this and call it a QlikFact!
So to reiterate, don’t try to build a multi-dimensional visualization when you’re only trying to express a single number. I’ve found the factoid to be particularly useful when expressing KPI values, especially when there are well-known, impacting SLAs established and business users are keen to see performance numbers.
Keep on Qlik-in’
Bill
I used a great QV feature for the first time this week – the Color Mix Wizard. Insofar as it is indeed a Wizard, there’s not a lot you need to know to use it, but I’ll walk through the steps I took…
The objective of this exercise was to come up with a visualization for looking at the number of survey respondents based on their answers to two different questions. My first attempt was to use a scatter plot, but that failed miserably since my dimensions (the x- and y-axes) were not continuous numbers, but rather ordinal values. Even when I encoded those ordinals as integers, the results were not what I was looking for.
SO…enter the grid chart.
Having worked through this example helped me clearly articulate the use case for the grid chart – basically, it is a version of a scatter chart that works for categorical values, be they nominal or ordinal. Nominal values are labels like “US, UK, FR” or “Finance, Sales, Operations.” Ordinal values are also categories, but can be arranged in a ranked order like “Under 25, 25-30, 30-35″. (for more info check out this Wikipedia article Level of measurement)
Here is my grid chart with two dimensions and bare-bones formatting:
Try as I might, I could find no simple way to make each horizontal row the same color…sigh…
So that grid chart tells me relatively how many people responded to each combination of question 1 and question 2. And it also provides some correlation information – for example, there are a large number of respondents falling into x-category: “18-25,” y-category: “4-6 hours.” This information is useful in and of itself, but I also wanted to compare it to their responses to a third question – a simple YES/NO question.
I could have opted for the pie chart style (from the Style tab) … but I’m already breaking Stephen Few rules by encoding magnitude as the radii of the bubbles … best not make it worse with pies. Moreover, I personally don’t care for the aesthetic of the pie grid. Regardless, had I chosen this route, I could have simply used the third question as a third dimension and the pie chart would have proportioned two “slices” of pie – one for YES and one for NO. And included a legend too.
Instead, I decided to use a varying color saturation on grid bubbles. Since this will require a single number (vs. two categorical values – YES/NO) I needed a way to encode the survey responses to a single number. Hence I chose to take the ratio of YES responses (i.e. # of YES responses divided by # of ALL responses) which results in a floating point number between 0 and 1.0.
Now with a single number I can use it as a parameter to control color saturation. For example – I could simply use it as the alpha value (multiplying by 255 or similar), but I wanted finer control … especially at the “light” (faint) end of the scale. This is where the Color Wizard comes in!!
First of all, it’s not obvious where to find this wizard…
Take some time to play with the wizard – it may take a few passes to get the effect you want. A couple of key parameters to explore:
Enhanced Colors – depending on the distribution of your data you may or may not want to select this option. It’s effect is analogous to a stereo enhancer for audio in that it takes the “middle” and pushes it out to both “sides.” Which in our case means taking the middle values and pushing them more towards to the upper and lower ends of the colors you’ve chosen.
Value Saturation (upper/lower) – again, your use case will dictate how to set this. If your data has long tails, you may want to set these limits so they group the outliers into a common color.
The Color Wizard will generate a piece of code that should look something like this:
=ColorMix1 ((rangemin(.8,rangemax([Legend Value] ,.1))-.1)/(.8-.1), ARGB(255, 242, 242, 242), ARGB(255, 128, 0, 0))
You can identify the Value Saturation range values (0.1 and 0.8) and the RGB values for the colors, so if you need to tweak the expression, you can do it directly on the code, instead of re-running the wizard.
NOTE: if you use Enhanced Colors you’ll get a much lengthier piece of code!
So now I have a true heat map, with the darker red bubbles indicating more YES responses to Question 3:
Finally, it would be nice to have a more quantitative explanation of the heat levels besides the text: “darker means more YES responses.” So, a simple legend can be easily implemented in a straight table chart. This table will have just three columns:
The expression really doesn’t matter because we’ll “white” it out (i.e. white text on white background).
Here is the load statement for the inline table:
LegendSaturation:
LOAD * INLINE [
Legend Label, Legend Value
< 10%, 0.1
20%, 0.2
30%, 0.3
40%, 0.4
etc...Â
];
After reloading the script, create a Chart object and select the Straight Table option. (I like Style = Table 1 for this example.) Choose Legend Label and Legend Value as the two dimensions. The expression column doesn’t matter, you can use something simple like only([Legend Value]) or sum([Legend Value]).
Now apply the colors…
That should do it! Resize the columns a little bit to balance the shape and you should have something that looks like this:
Finally, putting it all together:
Anyone who has worked on front-end development tasks in QlikView knows what a drag it is trying to reposition objects without a caption bar. Well…those days are done! … with version 10.
In version 10, objects without (or with) captions can be easily moved by holding down the ALT key and dragging them with the mouse.
A great way to provide your end-users added insight from a bar chart is to highlight a special dimension value.
What is a “special” dimension value?? For example, if you’re reporting on market share between your company and competitors, you should highlight “Our Company” on the chart. Stephen Few has a nice example on this topic in “Information Dashboard Design,” Chapter 6.
Another example would be if you’re a Product Manager and are looking at sales figures across various products in the line. By highlighting the specific product that you manage you can see where it stacks up against the rest … at a glance.
Imagine we really want to focus on Product = Alpha, and let’s highlight it with a saturated red bar:
So, naturally we need to figure out how to do this in QlikView, right?!
Fortunately there is a nifty feature in the Expression Properties of the Chart object that allows you to dynamically control the Background Color of the bars. And because it itself is an expression you can use if-then logic or any other expression you can conjure.
Here I’ve chosen to use the red() function, but of course you can use any similar color function or rgb().
So this solution is just fine, except it requires that the dimension of interest be hard-coded into the expression in the chart properties. That may be okay for static analysis (like the market share example above – our company vs. our competitors). But for something that could change frequently (like Product) it would be a much more sustainable solution to be able to dynamically control the dimension of interest.
One way to make the dimension of interest dynamic is to store it in a variable. And then drop an Input Box object on the page. In this example we’ll call the variable varHighlightedProduct.
Now with a simple change to the Background Color expression –>
if(Product = $(varHighlightedProduct), red(255))
we can use an input box to control the highlighted dimension.
The variable method works well enough, but changing the value in the input box requires a little bit of typing. Wouldn’t it be easier if there was a list box object in which the end-user could just select the dimension value of interest?? (answer = “YES”)
To build a separate list box we obviously need a data field. That can be easily accomplished with a resident load. Let’s suppose the sales data are stored in a table MySales, and the dimension field for product name is simply called Product. Then our load statement looks like:
HighlightedProduct:
LOAD DISTINCT Product AS [Highlighted Product] RESIDENT MySales;
This will load the unique values of Product into a table called HighlightedProduct with one field, [Highlighted Product].
Next, modify the Background Color expression so that it’s comparing against the selection in [Highlighted Product] –>
if(Product = [Highlighted Product], red(255)).
(You could get fancier with this code…allow multiple selections, restrict the list box to a single value, etc.)
So now we have a very convenient way for the end-user (the Product Manager in this example) to switch the dimension of interest using a simple list box.
I hope you find this simple example useful. Let me know if you have other ideas around this topic!
Okay, I confess I didn’t really “get” this feature when I saw it in the webex demo. But after seeing it again at Q Days in San Diego last week, the lightbulb finally turned on for me.
I’ll do my best to explain it, but I highly recommend that you go check it out yourself at the QlikView v.10 demo site. The application “What’s New in QV10″ is a good place to start.
In version 9 a “simple” search function can be achieved by dropping a search object on the page. And then you can configure the properties to search across all fields or narrow it down to specific fields. This is a very effective tool and end-users love it. But it’s not really associative. By that I mean, it’s a direct search; i.e. if I’ve set up the search object to search in the Salesperson field, then I enter some portion of the Salesperson name in the search box. For example, if I know the salesperson’s name is Sharon something, I type “*sha*” and a list of Salespeople with the substring “sha” in their name shows up. Make sense?
But what if I don’t remember any part of the Salesperson’s name, but I remember some other things associated to the salesperson. Like what they’ve sold. Or to whom they’ve sold …
In version 10 associative search, I can search for Salesperson by entering keywords about other fields that are associated to Salesperson (like Customer, Product, etc.) So in the example above, if I couldn’t even remember any part of the Salesperson’s name, BUT I did remember she works in Germany (Business Unit) and sells Milk (Product) and sells to ActiveDesign (Customer) then I can use associative search and hopefully zero-in on her…
This series of screen shots walk through the scenario I outlined above.
Step 1 – I open the search bar on the Salesperson list box (the magnifying glass) and type “germa”
Step 2 – I click on BusinessUnit_EN =Â GERMANY
Step 3 – I then type “milk” in the same search bar
Step 4 – I click on Product Sub Group = Milk
Step 5 & 6 – Finally I type “activ” in the search bar and click on Customer = ActiveDesign
Step 7 – with these associations, my list box has been reduced to one value – Sharon Carver –> I have my answer!
Step 8 – Optionally, I can explicitly select Sharon Carver and filter my whole model to her for further analysis
So…another great feature for end-users coming in version 10!
One of the great sessions at Q Days 2010 San Diego (yesterday!) was “QlikView Under the Hood,” presented by Daniel English of the Product team.
I’m a scientist at heart and really like to know how things tick. Sure, it’s rewarding to deliver a stunning dashboard to my business partner and hear them say “wow” or “we didn’t have this type of intell before” … but, in the back of my mind I’m wondering “how much RAM is this really consuming?” Â :)
So, this session addressed some questions. The two big takeaways for me were:
So yeah, Associative really does mean something different than most SQL query tools. Unless of course your SQL tools allows you to query every table in your mart simultaneously
© 2013 The Qlik Board! · Proudly powered by WordPress & Green Park 2 by Cordobo.
Valid XHTML 1.0 Transitional | Valid CSS 3
