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.
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
Used Dimensions. In same tab, put in a text on
Label option, like
Year. Click on
Next button again and enter the following formula:
Change some chart properties as in the following instructions.
- From Expression tab, fill
Labelfield with a text like
Values on Data Pointsoption.
- In Presentation tab, change
Cluster Distanceto three (3).
Plot Values Inside Segmentsoption. Select
- Go to Axes tab and select
- In Colors tab change first color as you want.
- Advance to Number tab and change number format to
- In Layout tab, configure
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
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
- In Number tab, change Variation expression to
Fixed to 1 Decimal.
- Back to Expressions tab, opens
Variationexpression clicking on plus signal (+).
Definitionfield 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?