List Your Values

We all have our own set of values. Some value love. Some value money. But just so we're clear, in the righteous words of David Lee Roth, money can't buy happiness but it can buy a huge yacht that sails right next to it.

Some value knowledge, but fear that we can't gain it. Fear of our own personal El Guapo. In a way, all of us has an El Guapo to face. For some, shyness might be their El Guapo. (Shyness is my El Guapo) For others, a lack of education might be their El Guapo. For our fearless leader, JD, being muy guapo is his El Guapo. For all of us, El Guapo is a big, dangerous man who wants to kill us. But as sure as my name is Lucky Day, the analysts of MarketStar can conquer their own personal El Guapo, who also happens to be the actual El Guapo! Which, translated from traditional Swedish means 'Qlikview'.

Why am I preaching values, you ask? Because I'm going to teach you how create your own list of values, or 'ValueList', if you will.

Maxed out

Today is Valentine's Day. Tomorrow you will be crushed by a 143,000 ton asteroid. It's worth almost $200 BILLION, so you should be able to rebuild your deck and get a new hot tub. Maybe even an name-brand Jacuzzi ! The only problem is that you only have a 1 in 3 Trillion chance of cashing in. Better if your house is bigger.

Movoto

While you're waiting, let's learn a new tidbit about the Max function.

Fact: Bears eat beets

It's April first. Your sales manager, Joe McImpatientpants, pulls up his dashboard and immediately calls you. Through the screams of how crappy your dashboard is, you divine that he's looking at the 'Current Quarter' tab. He's upset that everything is '0'! You take a deep breath to try to explain to him that his sales are '0' because it's the start of a new quarter and there haven't been any sales reported yet. You, of course, fail at this explanation because Joe McImpatientpants looks bad because his sales are '0'! Fix it, dammit! He forgot to slam his hand on his desk, so you don't take it seriously.

Since you're an analyst and you were hired because you always think you're right, you politely say that you'll fix it, hang up the phone, and call Joe McImpatientpants a few choice words under your breath.

Breathe. Smile. But not like a crazy person. More like Gandhi or a cat. But not like a monkey. Because showing your monkey teeth is a submission signal. When someone smiles at me with their monkey teeth, all I see is a chimpanzee begging for its life.

To fix it, we'll show the previous quarter's data for the first couple of weeks, then switch it to current quarter.

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.

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.)

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

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!