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…

Just like we made the transition from long division to an LED calculator, we’re now making the transition from LED charts layered on top of other charts to ‘Text in Chart’. The benefits are endless:

• Simplifies the look of your document
• No need to guess at the number of digits
• Exports with the chart
• No layering problems
• Better formatting options

Today’s tip had two parts. The first is ‘Text in Chart’. Once you’re a pro with ‘Text in Chart’, we’ll cover ‘Reference Lines’.

Text in Chart

First, create yourself a Bar chart. Next, create an LED chart. Put the LED chart on top of the Bar chart. You end up with something like:

Most of you will look at this chart and throw up a little in your mouth. The bright green of the LED chart clashes with everything. The LED background is white and the plot is grey. It’s just hanging out there, aligned with nothing. The title is a different font than the Bar chart. The rest of you don’t read my blog or go to my trainings. Let’s fix it. 

• Kill the LED chart
• Change your plot area to minimal
• Add a Text in Chart to show total sales
• Go to the Presentation tab in the chart properties. Click the ‘Add’ button next to Text in Chart.

We are going to add two text boxes. The first is ‘Total Sales’. In the expression box, enter an expression similar to:

     ='Total Sales - ' & Num(Sum(SalesAmount),'$#,###.')

• Don’t forget to use the ‘Num’ function to format your value to dollars
• Change the font to match your chart
• Right align the text
• Click OK

.

By default, Qlikview puts the new text box at the top left of your chart:

Use Ctrl-Shift to move the text box to an appealing location. In this case, the top right. Already much better!

Next, let’s add a text box that let’s your users know which years’ data is showing in the chart. Follow the steps above, then add an expression similar to:

     ='Sales are for ' 

          & 

          If(GetSelectedCount(Year) = 0, 'all sales in database',

          GetFieldSelections(Year)) 

This expression is conditional. If you do not have a Year selected, you will see “Sales are for all sales in database”. If you have a Year (or Years) selected, you will see “Sales are for 2007, 2008”.

Bonus!

Since the dashboard tradeoff is space vs. information, let’s add a reference line to the chart. This example shows average sales, but it could be any info that helps your users. Back to the Presentation tab, click the ‘Add’ button next to Reference Lines:

In the Label expression box, add an expression similar to:

     ='Avg Sales per Country  ' & Num((Sum(SalesAmount) / Count(DISTINCT Country)),'$#,###.') 

In the Definition expression box, add an expression similar to:

     =Sum(SalesAmount) / Count(DISTINCT Country) 

What you’ve done here is to add a reference line that will react to your filters. 

With practice, you’ll be able to adapt these techniques to any of your charts. Before you know it, you’ll make Bodhidharma flush with anxiety.