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.
Trend
.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 v12month
is less than the current month, then use the sum of the data we have in Sales 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:
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.
Be patient as it may take a minute to make the switch and refresh the dialog contents:
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.
Third-degree polynomials are algebraic expressions using numbers multiplied by themselves. For example, X * X * X
is represented as X3
, and a polynomial algebra expression would be represented by y = 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.
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:
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: