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 ABOVE
function 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 likeSales Total
. - Select
Values on Data Points
option. - In Presentation tab, change
Cluster Distance
to three (3). - Select
Plot Values Inside Segments
option. SelectVertical
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
to0 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