Skip to Content

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 1st Jan to 31st 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.

/wp-content/uploads/2015/01/month_tbl_622117.png

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:

/wp-content/uploads/2015/01/calc_view_622150.png/wp-content/uploads/2015/01/arw_622157.png/wp-content/uploads/2015/01/calculations_622155.png

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. 1st of January. The data type of CURRENT_DATE is date.

/wp-content/uploads/2015/01/cy_start_of_year_622123.png

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

/wp-content/uploads/2015/01/cy_date_622139.png

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.

/wp-content/uploads/2015/01/py_current_date_622144.png

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

PY_Start_of_year.png

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.

/wp-content/uploads/2015/01/join_622158.png

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

/wp-content/uploads/2015/01/model_622179.png

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.

CY_YTD.png

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

PY_YTD.png

  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:

/wp-content/uploads/2015/01/prompt_622163.png

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.

    /wp-content/uploads/2015/01/result_all_622164.png

  2. Filter on month 9 – results are shown from Jan – September for current and previous year.
    /wp-content/uploads/2015/01/result_9_622165.png
  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.
    /wp-content/uploads/2015/01/result_6_622167.png

/wp-content/uploads/2015/01/result_6_dt_622168.png

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.

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

  1. Ramana Krothpalli

    I have few questions.

     

    1. There seems to be a filter on the Projection_2. What is it? I am interested to know how many years of data is selected from the Analytic view.

     

    2. How is the dimension CURRENT_DATE defined?

    (0) 
    1. Guneet Wadhwa Post author

      Hello Ramana,

       

      1. The filter is set as optional for the user to enter a month into the query, which is part of an input parameter. This option is entirely up to the modeller, they can have a model with input parameters or without.

       

      The analytical view is bringing 4 years of data.

       

      2. CURRENT_DATE is defined as now() inside the analytical view.

       

      Thanks,

      Guneet

      (0) 
      1. Jaspal Singh

        Hi Guneet,

        I have similar requirement of MTD, YTD calculation. Need some inputs for this from you.

        I have 1 fact and Multiple dim tables in HANA Analytic view.

         

        The fact table is having data like 2013-01-01, 2013-02-01, 2013-03-01, 2013-04-01 and a separate field with Year and a separate field with Month int he fact table. I need MTD YTD Calculation on this for Revenue. Could you please elaborate how to develop in this case.

        (0) 
  2. Robert Tan

    Hi, would like to ask how do i add Quarter into the file?

    Do i add another flag for Quarter?

    Thanks and much appreciated for your help.

    (0) 
    1. Guneet Wadhwa Post author

      Hello Robert,

       

      Apologies for the late reply.

       

      You can add the QTD, by adding the Quarter column to the existing time dim file so that the data looks like this:

       

      FLAG MONTH QUARTER
      1 1 1
      1 2 1
      1 3 1
      1 4 1
      1 5 1
      1 6 1
      1 7 1
      1 8 1
      1 9 1
      1 10 1
      1 11 1
      1 12 1
      2 2 1
      2 3 1
      2 4 1
      2 5 1
      2 6 1
      2 7 1
      2 8 1
      2 9 1
      2 10 1
      2 11 1
      2 12 1
      3 3 1
      3 4 1
      3 5 1
      3 6 1
      3 7 1
      3 8 1
      3 9 1
      3 10 1
      3 11 1
      3 12 1
      4 4 2
      4 5 2
      4 6 2
      4 7 2
      4 8 2
      4 9 2
      4 10 2
      4 11 2
      4 12 2
      5 5 2
      5 6 2
      5 7 2
      5 8 2
      5 9 2
      5 10 2
      5 11 2
      5 12 2
      6 6 2
      6 7 2
      6 8 2
      6 9 2
      6 10 2
      6 11 2
      6 12 2
      7 7 3
      7 8 3
      7 9 3
      7 10 3
      7 11 3
      7 12 3
      8 8 3
      8 9 3
      8 10 3
      8 11 3
      8 12 3
      9 9 3
      9 10 3
      9 11 3
      9 12 3
      10 10 4
      10 11 4
      10 12 4
      11 11 4
      11 12 4
      12 12 4

       

      Thanks

      Guneet

      (0) 

Leave a Reply