Trend analysis enables users to compare actual totals against measures derived from the data. This will enable users to compare actual results against derived amounts, and even project those amounts into the future. This example will use a simple average as the basis for comparison. This will require small modifications to the ElastiCube, and most of the work will be done in Sisense Web.
ElastiCube Level Changes
The following image shows the ElastiCube being used for this example. Descriptions of each table, and the necessary changes are described below.
SalesOrderDetail: Contains product level information about each order, such as order quantity. Was not modified for this example.
SalesOrderHeader: Contains order level information, such as customer and ship date. Was not modified for this example
Date: Flat file with dates that go past the current date, which was taken from this article: https://support.sisense.com/entries/60889430-Date-Dimension-File . There is a custom column on this table called ‘Dummy’ that is set to a static value of 0. This dummy value will give a base value for projecting information against dates that don't exist yet in the sales data.
Dashboard Widget Level Changes
The following image shows the trend analysis in Sisense. Its based around quantity sold per month. Descriptions of each formula are described below
Blue: The monthly totals of order quantities. The order quantity is taken from OrderDetails .
Red: The average of the monthly order quantity totals, plus the dummy value. The date field is taken from the Date table, not the OrderHeader. Uses multi-pass aggregations, which you can learn more about in our documentation.
Yellow: The running sum of total order quantity, from the beginning of the year. It’s a cumulative view of the blue line
Green: The running sum of the average monthly order quantity. The easiest way to create this is to save the red line function, reference it in a new value, and then use the built in quick function for running sum since the beginning of the year. In this example we saved the formula as Average Monthly Quantity
Now we can compare the actual running sum of order quantity (yellow line) to the running average over this period (green line). We can also infer how much quantity should be sold by the end of the quarter, based upon our average.
