The image below shows a common support request - a chart showing a measure over time.
In the example described here, it shows the current month and the previous three. Such a chart updates as the current epoch changes to always show the latest measure and the ongoing performance. We have assumed below that use is of default monthly epochs rather than custom epochs.
Such a chart is best constructed using queries like those shown in image the image below.
The Find this month and previous three query is a general purpose query that starts from <the current epoch> and uses the Find sibling epochs block (in the Miscellaneous group) to also return the previous three months. Note that the Range between setting must be set in a linear date sequence. For example, -3 to +3 will find three months either side of the current month, but +3 to -3 won't find anything.
The Find Measure Instances for this month and previous three filtered by Type query finds and orders the measure instances that are plotted on the chart. The key points about this query are:
The starting point is the parameter. This helps make the query reusable. In the example, this is the
Live Projects element.
The Find Instances from Container Element block is a Measures block. This block looks for measure instances belonging to the container element. In this example, the container element is Live Projects.
The where Instances Epoch condition finds measure instances where the epoch that the instance belongs to is one of the epochs found by the Find this month and previous three query. This ensures that you only get measure instances for this month and the preceding three. To add the query to the condition, next to is one of, click the down arrow and then, on the drop-down menu, click <choose elements using a query>. This adds the box where you can drag and drop the query.
For illustrative purposes only, the list of measure instances are further qualified by measure type. Again for reuse reasons, this is set to a variable. In the example, the results are filtered by the Cost measure type.
The And Finally block is essential to ensure that the measure instances are plotted in date order. This is an order by date field block. When you add this, next to order by, click the down arrow and then, on the drop-down menu, click <choose a field from a related element>. This gives you the box where you can then drop the Find Instances Epoch block. Setting this to Start Date ascending ensures the correct ordering. Without this And Finally block, the ordering of the measure instances would be alphabetical (a common issue encoutered when creating charts like this).
To make use of these queries in a chart (the example shows a Line chart and supporting images can be seen in image below):
Subject of panel is pinned to the element whose measure instances are to be plotted.
Information shown is set to the Find Measure Instances for this month and previous three filtered by Type query. The Subject of panel element is passed to this query by means of the query's the parameter setting.
In Select Fields, the Y series value is set to the Actual for a measure instance. On the Inputs tab, The Current Epoch and Measure Type variables are set.
Naming conventions are set so that you just get the name of the month along the X axis and not the fully qualified measure instance name. With the chart selected, on the ribbon, on the Settings tab, click ABC. In the Naming Convention dialog box, set Measures to Epoch.
Together these queries and the chart settings give you the chart plotting the measure instances for this month and the previous three.
If you would like to read the full document, the article can be found via the attached word document.
I feel this should be pinned somewhere. It's something I do not have to do particularly often, but when I do it's a real slog to find the information to actually make it happen. Let's just the the path there is not very intuitive.
Please sign in to leave a comment.