Introduction

This document shows you how to include arrows on charts to compare year on year data. This technique is useful to compare increase or decrease between periods, highlighting with a arrow if actual year is higher than previous year, or not. To do it, just ABOVEfunction is needed in one more expression, turning it so simple. To create chart at side you can use the data sample below.

Using the ABOVE function let your expression more simple and easy to understand. No SETANALSYS techniques is necessary to put arrows sign in the X axis. On chart at side is possible see how much money a company had when selling its products, year to year. Its a very simple bar chart. Under each data value, chart is showing if a year was better than previous year, or not, using billing perspective. When a arrow is in up direction, it means that year was better than previous one. In other hand, if arrows is in down direction, billing on that year was less than previous one.

To create this Chart, open a new QlikView Document and open its Script Editor pressing CTRL + E shortcut. After, copy the following data samples and paste it below of variable definitions. Save your document and run its script.

SalesReport:
LOAD * INLINE [
SalesYear, SalesTotal,       Cost
2000,         1937770,    1760181
2001,         1712212,    1717546
2002,         1303098,    1301762
2003,         1842440,    1668624
2004,         1652343,    1682694
2005,         1455939,    1417934
2006,         1242664,    1242524
2007,         1798303,    1606255];

Creating a Bar Chart

After load data in memory, create a new bar chart accessing Chart from shortcut menu. Select the bar chart type (first one) and click Next. When Dimensions tab is showed, add SalesYear as Used Dimensions. In same tab, put in a text on Label option, like Year. Click on Next button again and enter the following formula:

=SUM(SalesTotal)

Change some chart properties as in the following instructions.

• From Expression tab, fill Label field with a text like Sales Total.
• Select Values on Data Points option.
• In Presentation tab, change Cluster Distance to three (3).
• Select Plot Values Inside Segments option. Select Vertical option, also.
• Go to Axes tab and select Hide Axis option.
• In Colors tab change first color as you want.
• Advance to Number tab and change number format to Integer.
• In Layout tab, configure Border With to 0 pt.

To finish this section, go to Caption tab and fill the Title Text field with a text like Total Sales by Year. Use the time to change both Inactive Caption and Active Caption to white. Using Font button, this example will change font size for title to 14. Uncheck both Allow Minimize and Allow Maximize options.

  Note:  On Chart below was used RGB 204, 204, 204 for bar colors.

Creating Arrows as Indicators

Back to Expressions tab, create a new expression using the following formula:

=Num(([Sales Total] - Above([Sales Total])) / Above([Sales Total]), '▲ #.##0,0%; ▼ -#.##0,0%') & Chr(13) & Chr(10) & ' '

Label this formula as Variation, in Label field. Take a look that Sales Total was used as input for this new formula. Sales Total is, in fact, label for previous formula. When Above is called it will use previous value from Sales Total to calculate variation from previous year. Of course that first year will not be filled with a variation, because no previous year is found. For this example, variation expression is using the following formula:

(SALES_FOR_A_YEAR - SALES_FOR_PREVIOUS_YEAR) / SALES_FOR_PREVIOUS_YEAR

As a result a variation percentage between years is calculated. To fill arrows, the NUM function is used to define its format, including arrows caracteres. This function has the following sintaxe:

=NUM(value_or_field, positive_pattern, negative_pattern)

After create a new expression and click Apply it’s possible see new values on chart. But, we would like to show values only in X axis. For that, on Variation expression, uncheck Bar and select Text on Axis. The function Chr(13) followed by Chr(10) and a space, instructs to Qlikview break a line between values with arrows and labels to years. Otherwise, all values will be showed stacked. Some final adjustments are necessary to get a good chart.

• From Presentation tab, uncheck Show Legend option.
• In Number tab, change Variation expression to Fixed to 1 Decimal.
• Back to Expressions tab, opens Variation expression clicking on plus signal (+).
• Select Text Color option.
• In Definition field enter the following formula:

=IF(([Sales Total] - Above([Sales Total])) / Above([Sales Total]) > 0, Green(), LightRed())

This expression in Text Color will paint arrows and its values according to sales results between pairs of years. Green is a function that represents a color. And lightred is another one that represents a strong red. Ready?

Used Functions

• SUM
• ABOVE
• IF
• NUM
• CHR