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!

The first thing you need to know is that all seven attributes are not available on all charts. So let's go through them. But first, how do you get there?

Background Color

Background color allows you to change the color of your bars, lines and cells. You can use any of Qlikview's color functions such as BLUE(), ARGB(), or RGB(). For example, if you click on Background Color and enter GREEN() in the Definition, you can change the bar color from the default to green:

That may or may not be useful. Better, you could add an expression to vary the color of the bar. Let's say that you want to highlight the city with the highest sales by making that bar green, while leaving the rest red.

Enter the following expression in the definitions box:

     If(Rank(Sum(SalesAmount)) = 1, green(), red())

And magic, you get:

Text Color

Go back and re-read Background Color because you do it the same way.

Don't smoke.

Text Format

You can use most basic HTML formatting with this attribute. The most common that you might use are:


     <B>         Bold
     <I>         Italic
     <U>         Underline
     <H1> through <H6> Size


To make these work in Qlikview, you must wrap them in single quotes:

     '<B>'

If you want to make the text Bold, Italics, Underlined, simply add the tags inside the single quotes:

     '<B><I><U>'

Pie Popout

Since you're not super lazy and you don't use pie charts, you can skip this section. If you want to add a pie chart as a joke or to kill kittens, read on...

'Pie Popout' explodes your pie chart. Enter a 1 in the defition box to explode all of the sections

A better option is to explode only the piece of pie that you want to highlight. We'll steal the most sales example above. Enter the following in the definition:

     If(Rank(Sum(SalesAmount)) = 1, 1, 0)

Since we left in the color highlight, not only will the city with the most sales be green, it will also explode from the pie.

Bar Offset

Most likely only useful when constructing a waterfall chart, this setting lets you move your data up or down from the zero point. In the chart below, I've offset London by 500,000:
   

We'll cover waterfall charts in another post.

Line Style

The Line Style attribute lets you change both the style and width of the line. Similar to the Text Format, you will need to wrap the commands in both single quotes. Style syntax is as follows:

     '<S1>' Continuous
     '<S2>' Dashed
     '<S3>' Dotted
     '<S4>' Dashed & Dotted


Width syntax is similar, but starts with a 'W' and can range from 0.5 - 8 ('<W8>'). To exaggerate for the example, we'll make the line Dashed, Dotted and as wide as possible. Enter '<S4><W8>' in the definition box.

If you make a chart this ugly, you better have a darn good reason.

Show Value

This one I like. One of the ugliest things you can do is to add values to the data points on a line chart. It might be useful, however, to only add a value to the largest or smallest value. Doing so would hightlight a data point without overwhelming your users with a bunch of jumbled numbers stretching across your graph.

Like we've done above, enter the following expression into the definition box:

     If(Rank(Sum(SalesAmount)) = 1, 1, 0)

Magically, the value of the largest sales appears on the endpoint:

CAUTION! If you have 'Values on Data Points' checked, it will override your Show Value attribute.

You can get as fancy or as simple as you need to with these attributes. The rule is just like my color rule:

     If you think it's just right, bring it back a step or two.

P.S.

Dimensions have attributes, too.

P.P.S.

Instead of using RGB, use ARGB so that you have control over the opacity of your colors.

     A      Alpha (opacity)
     R      Red
     G      Green
     B      Blue

All four values range from 0 - 255.