Two-tone charts…and the challenges of managing multiple series:
One of our customers came with an interesting request:
What if you change the graph to a dotted line where it changes from actuals to forecast?
Jedox charts are a great way to provide a quick overview of your data. Most of us have used graphs before and are familiar with the basic features. However, some may have struggled to create a graph that goes beyond standard.
The mentioned request led to such an example.
At first you would expect that this is not possible with Jedox charts. And yes, Jedox charts do not have a feature of creating a dotted line. The alternative would be a different color and works like a charm. We can define it manually.
Let’s say you have monthly FTE numbers of a department. Actuals are available up till and including July. The actuals graph should be ‘extended’ with a forecast towards the end of the year. Here’s a simple and effective set-up.
- Create a table with two series:
- one for your actuals,
- one for your forecast
- (and another one for your budget if you like)
2. The header row contains the months
3. In July, copy the realized value to the forecast and start forecasting from August
The data range for your chart would look like this:
Next, set up a line chart based on that data range. Jedox will automatically group data properly (your series are grouped by row).
Change the colors of the graphs as you like. In this example, the blue line is dark for actuals and light for the forecast. Right-click anywhere on the series chart and choose ‘Format Data Series’:
Line chart vs. XY scatter plot
The above example shows how easily you can work with different series in a line chart. So what about a scatter plot? It may look similar, but the data range needs an important change.
Main reason: A line chart makes use of one category for all series whereas a scatter plot uses one category per series. So you need to include x-values for Budget, Forecast and Realized. Please note that the series labels (first column) are put on the row of the category values (x axis).
The data range for your chart would now look like this:
Next, set up a scatter plot based on that data range. Jedox will not automatically group data properly. Select the following options:
Tips and tricks
- Each series added to the bottom of the table, will show on top of the others in the chart
- In the scatter plot, the automatic setting of major units on the category axis is 2. Change this manually to 1 in order to show all months.
- The series labels for the scatter plot source data are put on the row of the category values (x axis).
- Vertical grid lines may help the user to ‘read’ the chart. This is only possible in the XY scatter plot. Set the minor unit of the category axis to 1 and each month will have a grid line:
- If you have more series, you may consider changing some of them to another Chart Type.
Wishing you lots of creativity!
Download: Example *.wss file
Footnote: Line charts will evenly distribute values along the category axis, indifferent of their value. Take for example three data points at x=3, x=15 and x=16: the distance between 3 and 15 is the same as between 15 and 16: one unit. Therefore, both text and numeric values can be used. On the other hand, thescatter plot only allows numeric values on the category axis. As a result, data points will be plotted at a relative position along the category axis. As we may expect, there are 12 units between 3 and 15 whereas there is still one unit between 15 and 16.