In this article we will explain how to create a continuous line using multiple measures.
There are specific business cases where a line chart would be useful if one line ended and another continued on in a different color. An example of this is to have monthly actuals plotted up until the current month and estimated values plotted going forward.
Creating Custom Measures in ElastiCube Manager
Assuming that the 'Estimated' and 'Real' measures are two different fields, the creation of the two new custom measures is straight forward. This can be done in the ElastiCube Manager using and SQL statements similar to the ones below. These two statements assume there is a Month field in the available data set. The goal is to have values in the 'Estimated_New' measure during and after the current month, and values in the 'Actual_New' measure prior the current month.
Example of the table before the new fields:
WHEN Actual <> Null
WHEN Month >= getmonth(now())
WHEN Month <= getmonth(now())
Example of the table after the new fields are added:
Plotting New Measures in a Line Widget
Once the custom fields are built, add both of them to a line graph. The resulting 'Actual_New' graph should end on the prior month and the 'Estimated_New' graph should continue on the current month into the future. Note that when editing the widget, the 'Actual_New' should be listed second in the Values section. This will ensure that the measure will show for the previous months/dates.