Highlight the Trend, Part Deux

Well I guess it all started the first time I went through the second grade. I caught my reflection in a spoon while I was eating my cereal, and I remember thinking "wow, you're not like every other kid, you know, who dreams about being an astronaut, I was always more interested in what bark was made out of on a tree. Richard Gere's a real hero of mine. Sting. Sting would be another person who's a hero. The music he's created over the years, I don't really listen to it, but the fact that he's making it, I respect that. I care desperately about what I do. Do I know what product I'm selling? No. Do I know what I'm doing today? No. But I'm here, and I'm gonna give it my best shot."

Which brings me to:

     if([City] = '$(=GetFieldSelections([City]))', Green(), ARGB(150,225,225,225))

Definitely not my best shot.

Highlight the Trend

Because I went to the Derek Zoolander Center For Kids Who Can't Read Good And Wanna Learn To Do Other Stuff Good Too, I can now teach you really, really, ridiculously good Qlikview tricks.

This one's no different. It's a good way to cut through the clutter of a chart that displays a large number of lines.

Line charts are great tools for showing trends over time. Your user can easily visualize whether the data is getting better or worse by the shape of the chart. They are not good for charting distinct categories. A good example of a bad idea is to use a line chart to show sales by territory. A proper example is to chart sales over time, split up by city.

Scope Your Project!

Disney bought Lucasfilm for 4 BILLION DOLLARS. That proves their most prophetic quote in their most important movie, Spaceballs:

“So, Lone Starr, now you see that evil will always triumph, because good is dumb.”

Since you're not dumb – which you proved by knowing all about Joseph Luft and Harry Ingham’s Johari Window – you want to use a scope document when creating your Qlikview apps. The template outline below will make it easy and help you gather the pieces you need for success. You can also download a MarketStar-branded version from my Google Drive.

Overview

This is a short overview of Qlikview project. Include target audience, data sources, goals, questions to be answered, risks, and high level timeline. Use this section as an Executive Overview. Make sure that it can be used as an ‘elevator pitch’.

Label Expressions

This tip won't bring any money, but when you die, on your deathbed, you will receive total consciousness. So you got that goin' for you, which is nice.

Some peculiarities really annoy me. This is one of them...

Qlikview allows dynamic labels using expressions. So, when we have a table or chart that displays a rolling six months, the labels update with the data. This expression shows Mar 2012:

     =MinString({$<[TimeFrame.RelativeFiscalMonth] = {'Current Month - 6'}>} Calendar.MonthYear)

The problem is that a long, possibly very complicated, expression shows up in the Properties lists.

Container to Align, Size & Format

The Zen philosopher Basha once wrote, 'A flute with no holes is not a flute. A donut with no hole is a Danish.' He didn't say, ‘Two wrongs don't make a right, but three rights make a left,’ but it does.

Which brings me to my point. Follow my (non-religious but you should live them religiously) 10 Commandments.

Text in Chart

In the early ‘70s, those of us on the bleeding edge of technology used calculators with LED read-outs. Not only could we easily figure out the product of 908 X 57, but we could covertly write naughty words! We cleverly hid them by making you look at it upside down! 5318008 hasn’t been the same since…

Value Judgement

While I'm no pinko commie, you have to admire the cleverness of the claim:

"...help save or create at least two and a half million jobs."

My red, white and blue American version is:

"‘No’ most likely means ‘Not yet’."

That is so much less wishy-washy and definitely more clever.

Expression Attributes

We all have endearing attributes. Even me. My most endearing attribute is my humbleness. And my calves. You're most endearing attribute? You're willingness to obey my every command. That's nice.

Qlikview allows you to apply custom formatting to expressions. Maybe you want a dashed line instead of a solid line. Done. Your line is too skinny? Don't be stupid. Make it fat. Black text is so boring. How about grey? Speaking of grey, if you can guess the number of grey hairs that I have, you win a prize. Not a good prize.

I'm pretty sure that I'm up for the Congressional Medal of Honor because of FortuneCookieBI. Now, that's a prize!

Conditional Columns

One of the big issues we deal with is the lack of space in our dashboards. We tend to busy-up things by squeezing too much into a limited space. Stop it! there's a better way.  It's like magic.

Trellis Charts

Don’t make a chart like this! I dare you to try to make sense of it. You can’t. I’m not even going to try to list its faults. But its biggest fault is that it makes me angry. You wouldn’t like when I’m angry.

We can fix it.

Training PP for 8/8

Link to the training PowerPoint on Actions:

https://docs.google.com/file/d/0B_5TC7G8rWdKNTJ2V0pYcWpxY3c/edit

List of Actions

A list of Actions and their descriptions:

https://docs.google.com/file/d/0B_5TC7G8rWdKajZWTlB0WFV6MWc/edit

$ Sign on your Axis

I don’t know how many times I’ve been asked how to put a $ sign next to the figures on the y-axis. My answer has always been the same: you can only label the axis, not the values.

My rule of thumb: Never mess with a Sicilian when death is on the line. My other rule of thumb, probably more appropriate in this situation: ‘No’ most likely means ‘Not yet’.

m*Sight Training

Link to the PowerPoint from today's training:

https://docs.google.com/file/d/0B_5TC7G8rWdKLWZJUVRNVnBBZDA/edit

Hyperlink it!

Have you ever wanted to link to your CRM? Maybe a web site? From a table?

(For those of you who are not lucky enough to work at MarketStar, this example uses PartnerDynamics or 'PD'. MarketStar built PD to be the best partner relationship manager in the business.)

Easily Translate Comments

Hat tip to Jared Hatch for this awesomeness...

How many of you get comments in foreign languages? Spanish, Portuguese, Dutch... Wouldn't it be nice to simply click a button for a translation? For just three payments of $39.95, you, too, can have this power. But, wait! There's more!

m*Sight Training

Link to the PowerPoint from today's training:

https://docs.google.com/file/d/0B_5TC7G8rWdKQ1dha0wxandqckk/edit

My (non-religious but you should live them religiously) 10 Commandments

Whether we like it or not, presentation is almost as important as your data, conclusions or recommendations. Users will decide the worthiness of your work immediately based on their first impression and they will continue to judge as they go. In that spirit, let me present my ten commandments of design.

DON'T EVER DO THIS!

I know, two days in a row. In some countries, it would be considered spam. Communist countries.

But this is important. You should never do this. Ever. Unless you have express written approval from me. It does, however spotlight a creative way to use Qlikview. Consider this your express written approval to test this, but not to publish.

Enter:

=if(Odd(Second(Now(1))),Cyan(),Yellow())

Into any object’s background color. It will make it blink from cyan to yellow and back every second. Variations of the commands can be used for things like scrolling text, changing comments in a text box, etc. . A possibly positive use might be to blink the caption on an important warning, such as a known data problem.

Remember, with great power comes great responsibility. The fancy-pants among you might want to attribute this to Voltaire, be we all know it’s about Spiderman.

Hat tip to QlikView Maven for this trick.

Design Choices

Back in the 90s, I took a photography class. I loved it. Exposure, f-stops, focal length, mixing chemicals, dodge and burn, zone system, doing things in the dark. Which is weird, considering that photography is all about the light. With all the science and math involved in great photography, guess what the best tip to a good picture was? Frame your shot, then take one step closer. It simplifies your shot and keeps what’s important in the picture.

What's this have to do with dashboarding*? It seems we have a parallel path to better communicate the importance of our data. We have an immense amount of information at our fingertips, and the temptation is to show as much as possible.

Give your rows breathing space

This tip is about as easy as it gets, but it’s a great way to improve the look, feel and usability of your tables, especially if they only have a few rows. When you create a table, the default is to get something like:

The rows are mashed up and everything looks dense. To add some space, open the Chart Properties and go to the Presentation tab. At the bottom right, check the box ‘Wrap Cell Text’.

Bam! You have a table that feels much lighter:

Variables as Aggregators

Dashboards tend to be a compromise between space and information. Anything we can do to optimize our use of space will make our dashboards more useful and valuable. A good way to optimize your space is to use variables as your aggregators.

An example is the use of Sum and Average. Currently, if we want the user to be able to see both and save space, we’ll use a container or a button with conditional show. The problem with these methods is that you have to maintain two or more charts to keep them in sync.

Set Analysis with an Intersection

Every once in a while I come across a tip that makes me want to want to punch myself  break something  say ‘ah shucks’ because it’s so easy and it’s always bugged me.

Null Values

Some of my most frequent questions concern null values. How do I count them? How do I show them? The short answer is ‘you don’t’ because you’re trying to show or count nothingness. But there ways around the problem.

Select Excluded

Many of you use buttons to select a list box item:

Did you know that you can easily select everything BUT an item?

Go to Previous Sheet

Many of you use buttons to navigate among your sheets. Have you ever wanted to create a button that takes you back to the sheet you came from?


Easy-peasy.

1. Create a new variable named vSheetFrom
2. On your button that takes you to the new sheet, add a Set Variable action. In the value box, type =GetActiveSheetId()
3. On the second sheet, create a button with a Activate Sheet action. In the value box, type =vSheetFrom

Save and marvel at your cleverness.

Expression Cheat Sheet

In the words of the venerable Donald Rumsfeld, "Learn to say 'I don't know.' If used when appropriate, it will be often."

But, more appropriately for this tip, “…as we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns -- the ones we don't know we don't know." Of course, as we know and the media is too dumb to know, Rumsfeld stole this from Joseph Luft and Harry Ingham’s Johari Window.

In that spirit, I’ve created an Expression Cheat Sheet. It’s basically a list of all (or almost all) of the expressions in the Qlikview help files, grouped in a way that you can browse by topic to find ways of displaying your data that you may not know about. 

Download the file from Google Docs: http://tinyurl.com/expressioncheat

Percentiles, Part Deux

This week’s tip might seem a little difficult. But, TRUST ME, take it one step at a time and you’ll be amazed at how easy it really is!

This technique is a great way to maximize your available space by using variables to change a chart for different looks at your data. Your users will be happy that you put the effort in. Also, it doesn’t have to be limited to percentiles. We can modify it to change your dimensions as needed.

Quartiles

Time again for an m*Sight tip. You can find the original, with a document and sample data at Qlikfix.com. Qlikfix is a great place to get some great tips.

Using AJAX or the IE Plugin?

A very quick tip this week…

If you would like your users to identify whether or not they are using the IE Plugin or AJAX, create a simple text box and put in the following expression:

     = IF(ClientPlatform()='','Plugin',ClientPlatform())

It will return either the browser name or ‘Plugin’.

IE Plugin Problems

If your end users are unable to run your document with the IE Plug-in (after downloading and installing), have them check their security settings.

Variables as Expressions

If you use the same expression over and over in your document, you will love this tip! Take a look and if you have any questions on how to implement it, let me know.

Bar Charts

Bar charts are one of the best ways to communicate information. They are simple and easy to read. A few simple upgrades will take your presentation one step beyond.

Shortcut Keys

For those of you who love keyboard shortcut keys… Enjoy!

http://tinyurl.com/QlikviewShortcuts

Concatenating Dimensions

Occasionally, we need to concatenate dimensions so that multiple answer questions don’t duplicate rows. A good example is using multiple segments on the same partner or store.

Sort with Dual()

Creating Calculated Dimensions is a handy way to customize a chart or list box, but it has been difficult to get the sort order correct. Luckily, there is a simple way to get your sort by using the Dual() function. The Dual() function, in its simplest form, brings back two results with one expression. Using it with a Calculated Dimension will let you specify the dimension results along with a numeric value to use as a sort.

A simple example: I need to limit the Regions in a chart to three specific regions. The normal way is to write the following expression:

     = If([Region.Name] = 'CEE', 'CEE',
        If([Region.Name] = 'EUR', ‘EUR',
        If([Region.Name] = 'META', 'META')))

This will bring back only CEE, EUR and META regions.

Using the Dual() function, we add the ability to sort numerically (notice the additions in RED):

     = If([Region.Name] = 'CEE', Dual('CEE',1),
        If([Region.Name] = 'EUR', Dual('EUR',3),
        If([Region.Name] = 'META', Dual('META',2))))

If you use Calculated Dimensions, give this a try!

Default Open Date

The question “How do I default my document to the right date?” has come up a few times and I’d like to offer one possible solution using document triggers.