Simple example of Year To Date (YTD) calculation in SAP HANA

You have probably seen a few blogs on implementing Year to Date (YTD) calculations in HANA. A couple of these can be found at:

Implementation of WTD, MTD, YTD Period Reporting in HANA using Calculated Columns in Projection

How To…Calculate YTD-MTD-anyTD using Date Dimensions

These work well, however, I have created a simplified version of YTD calculations, which I used to deliver a Proof of concept at a customer.  The reason I call this a simplified version is simply because it has less steps and easier to implement with any data set.

As different models have different requirements; therefore, we have few assumptions here:

• The financial year is from 1^st Jan to 31^st Dec
• The year to date calculation is shown on a monthly basis.
• This model looks at Current YTD and Previous YTD calculations
• This model was built in December 2014; therefore, it refers to current year as 2014.
• We will have time dimension data created.
• The time dimension data will be joined with Sales table

Creating Simple Time Dimension table:

YTD is a time-based calculation, starting from the beginning of the current year, and continuing up to the present day. The following table is created to reference months in a year with a key field called ‘FLAG’. ‘FLAG’ column is required to filter the data according to the month. This column can be renamed to ‘Month’.

To create the Time dimension table, see SQL below:

CREATE COLUMN TABLE “SCHEMA”.“MONTHS” (“MONTH” VARCHAR(2),

      “FLAG” INTEGER CS_INT

      )

The data for the table is attached in this blog below.

The table contains data for 12 months, each month having 12 flags. The table should look like below once the data is loaded.

If requirement is Quarter to Date (QTD), then a quarter column can be added to the time dimension table.

Create a calculation view:

Assuming that an analytical view is already created, which contains the fact table (sales table).

Time generated data from HANA can also be joined into the model to bring fields such as Year, Month, Quarter etc.

1. In order to calculate the Start of the year and end of the year, following calculations can be created in the projection node:

Calculations:

CY_START_OF_YEAR: This calculation column is created to calculate the start of the year. It takes the last for 4 digits from current year and then adds 01-01 i.e. 1^st of January. The data type of CURRENT_DATE is date.

CY_DATE: This calculation column converts the CY_START_OF_YEAR calc column into data type ‘Date’.

PY_CURRENT_DATE: This calculated column calculates start of previous year. ‘addmonths’ function looks at current date and takes away 12 months to calculate date for previous year.

PY_START: This calculation column extracts the last 4 digits to get YEAR.

PY_DATE: converts the PY_START to date data type.

1. Join the sales analytical view with the Time dimension table created earlier.  Here MONTH is joined with MONTH.

    2. Create final two calculations at aggregation node to get CY_YTD orders and PY_YTD orders.

CY_YTD: In the calculation below, the code looks at the DATE_SQL column, which is the date the orders were created. If the column DATE_SQL is between the start of the year date and current date, then relevant orders are displayed. The data type of DATE_SQL is date and is extracted from Time generated data within HANA.

PY_YTD: This calculation does the same calculation as above but for previous year.

  3. (Optional) Creating an input parameter for entering Month as a filter. This is optional, in this model, the user can either get a prompt to enter month or a selection can be made at the report level. Notice here that the input parameter is on column FLAG, as FLAG is taken as an indicator for month. The input parameter looks like below:

Result:

Below is the summary of results shown as part of the YTD calculations. We want to see orders for current year and previous year, we have FLAG column as a filter. The results are shown in the Data preview part of the HANA studio, to enable this, click on data preview and then move to the ‘Analysis’ tab. Drag and drop relevant dimensions and measures.

1. No Filter – when there is no filter, the results are shown for all the months.

   

2. Filter on month 9 – results are shown from Jan – September for current and previous year.
   
3. Filter on month 6 – results shown from Jan to June. Notice, the second image shows results with Date field added, which shows that orders are pulled only from Jan – June.
   

This example gives you an insight into how the model solves YTD calculation in a performance friendly way. It also shows that by creating a few calculations you can avoid creating complex SQL procedures.  The model has been test with large sets of data as all the calculations are taking the advantage of the calc engine inside HANA and no extra wrappers are created.

The input parameter ensures that the data is pushed down at the right level to avoid transfers of large sets of data.

Please feel free to comment and provide your suggestions. If you have any questions then please do not hesitate to contact me.