We will start by adding a trend line to the lower chart, Monthly Sales and Margin. Then, we will add a forecasted trend line out 3 months to the upper chart, with a 12-month rolling average.
- Right-click on the chart titled Monthly Sales and Margin and choose Properties:
- Navigate to the Dimensions tab.
- Click on the Add button to add a new dimension.
- On the right-hand side in the Label box, type the word
Trend. - Next, click on the ellipses to enter the formula for our trend line:
if(v12month<=month(today()),sum({<YYYYMM ={">$(v12month)"}>}[Sales Amount]),avg(total aggr(if(v12month<=month(today()),sum([Sales Amount])),v12month)))Our formula says that, if the variable in
v12monthis less than the current month, then use the sum of the data we have inSales Amount. If we don't have the current month's data, then get an average of the aggregate of the last 12 months of data: - Make sure that the checkbox is checked and not the bar.
- Click on Apply and then click on OK.
Figure 6-1: Lower half of the Sales Analysis sheet of the Executive dashboard with trend lines
Notice how the trend line has been added to the chart and to the key across the bottom. Hovering with the mouse pointer over the trend line on the right-hand side of the taller red bars will display the exact number that the trend calculation produced.
Open the Properties wizard again. Now navigate to the Style tab where you can see a color preview of the chart with the yellow trend line added.
Next, go to the Colors tab. This is where the colors of our chart items are coming from. The left-hand side shows each color in our chart in a row.
- Click on the first colored rectangle to change the color used for the first item.
- When the Color Area wizard pops up, click on the rectangle in the upper-right corner and not the preview box in the centre-left.
- Choose a different color from the Color Picker.
- Click on OK to exit the Color Picker.
- Click on OK to exit the Advanced Color Map.
- Click on OK again to exit the Properties wizard.
Be patient as it may take a minute to make the switch and refresh the dialog contents:
Figure 6-2: The Advanced Color Map interface
In the Advanced Color Map interface of the Properties wizard, you may have noticed that you can have Sheet Default, Document Default, User Default, and QlikView Default. User associated defaults allow us to tailor our dashboards to specific colors for those people with a color vision deficiency. Colors can also be set via formulas and themes, which we will examine in the later section on the Trending tab.
- Now, right-click on the chart titled 12 Month Rolling Average and choose Properties.
- Navigate directly to the Expressions tab.
- Click on the expression named Sales in the box in the upper-left corner.
- Notice the box in the lower-left corner that is labelled Trend Lines.
- Now, using the scroll bar next to the Trend Lines box, choose Polynomial of the 3rd Degree.
Note
Third-degree polynomials are algebraic expressions using numbers multiplied by themselves. For example,
X * X * Xis represented asX3, and a polynomial algebra expression would be represented byy = ax3 + bx2 + cx + d, where x and y are the chart axes. This particular trend line type was chosen because it provided the most information with this chart data. Experiment with the other choices and decide for yourself. - Click on OK and review your QlikView created trend line.
Experiment with the other trend line options to become familiar with the results they produce. Notice that you can select several trend line types at once. Also, notice that they are all defaulting to the same color as the measure line so that we do not have the visually distinct color option as we do with creating our own trend formula, which is what we did in the first chart.
Open Properties for 12 Month Rolling Average, and return the current trend line display to a single line based on Sales using Polynomial of the 3rd Degree. Now we will extend that line into a forecast line:
- Navigate to the Axes tab.
- Notice that, in the lower-right corner, there are two boxes. One is labelled Backcast and the other is labelled Forecast:
Figure 6-3: Backcast and Forecast selection
- Check each box and enter a number to represent the number of periods to extend Forecast and the number of periods to extend Backcast.
- Before you close Properties, navigate to the Fonts tab, choose Size 8 for the list boxes and charts, and click on Apply so that the time periods will be easier to distinguish.
Rather than using a built-in formula, we can base Forecast on a custom formula-based trend line similar to the one we used in the first chart. The chart for 12 Month Rolling Average will now look like the following screenshot:
Figure 6-4: Backcast and Forecast displayed on a chart