Time Series Functions

Time Series Functions
These are the set of functions that use time as its measure to calculate the performance of the business by comparing historical data to the current data.

Three standard types of Time series functions:
1) Ago :- For finding the revenue a month/week/year ago.
2) ToDate :- For finding the revenue from a particular date till today.
3) Period Rolling :- For finding revenue from 1 point to other point in time

 Steps to Implement time series functions:

1) Identify time dimension

• In this step, we will identify a dimension table that has time related columns in it.Among all the dimension in BMM layer, Period is a dimension that shows us time related columns. Thus we identify Period Dimension as our time dimension as shown below:

• Now double click on PeriodsDim-->Go to General tab--> check Time box under structure as shown below to make PeriodsDim our identified Time dimension:

   2) Identify Chronological keys

• Right click on PeriodsDim and expand all as shown below

     

• Now double click and open Year, select the checkbox for Chronological key as shown below:

•  Similarly, make Quarter a chronological key at Quarter level, Make Monthcode as Chronological key at month level, Make YYYYMMDD a chronological key at day level.
• This is how to define chronological keys.

3) Create Ago measurable column

• Since all the measurable columns belong to Orders fact table, we will create AGO measurable column under ORDERS fact table. Say, if we want to measure revenue a month ago. 
• To do this, Go to Orders-->New Object--> Logical column--> check out--> General tab --> Name: Month Ago --> Column source tab --> select the radio button "Derived from existing columns using an expression"-->Edit expression button--> 
• Category: Functions
• Functions: Time series functions
• Time series functions: Ago
• Expression Builder: Ago(<<Measure>>, <<level>>,<<Number of periods>>)

    Here, In ago function, revenue is what we would like to check so in the first argument we should pass Revenue column, We want he data a month ago so the second argument gets month as level , third argument shows number of months you wanna go back. 

now click on ok-->ok.

• Drag and drop this measurable column on to the presentation layer under Sales subject area as shown below.

• Check in changes and save.

   4) Test the AGO measurable column

• Go to Analytics--> Create a sample report using Year, Monthcode, Revenue, Month Ago. Go to results tab and you will see all the Revenues for a month ago as shown below:  

Note that the first value is blank for Month Ago since there is no data available before 199801.
This is how we calculate revenue 1 month ago.

5) Create ToDate measurable column

• Similar to Month ago column, create YTD column to check revenue from starting of the year till date as shown below:  

• Drag and drop this column to presentation layer on sales subject area. Check in changes and save. Add YTD column to the report and go to results tab we can see the result as shown below: 

6) Create Period rolling Measurable column

• This is similar to ago and Todate functions. This example shows how to find out total revenue for last 3 months. Create new logical column under Orders face table called 3 months Period. the function periodrolling takes 3 arguments
• First Argument: Measurable column to calculate : e.g. Revenue
• Second Argument: Starting offset : we want to see data starting from last 3 months. for e.g. If it is December now then the revenue should be addition of revenues of previous 3 months , October and November  : E.g. -2.
• Third Argument: Ending offset: We want to calculate revenue till date : e.g. 0 it represents current date.

• Drag and drop this column on to presentation layer, check in changes and save.
• Go to Analytics and drag and drop 3 months Period column on report. Go to results tab as shown below: 

This is how we can calculate the revenue starting from X point in time to Y point in time.