cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

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.

Community_Admin_0-1634474064432.png

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

Community_Admin_1-1634474064435.png

Blue: The monthly totals of order quantities. The order quantity is taken from OrderDetails .

Community_Admin_2-1634474064366.png

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.

Community_Admin_3-1634474064362.png

Yellow: The running sum of total order quantity, from the beginning of the year.  It’s a cumulative view of the blue line

Community_Admin_4-1634474064285.png

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

Community_Admin_5-1634474064284.png

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.

Version history
Last update:
‎10-17-2021 05:35 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request