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 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.
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.
- In order to calculate the Start of the year and end of the year, following calculations can be created in the projection node:
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.
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.
- 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:
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.
- No Filter – when there is no filter, the results are shown for all the months.
- Filter on month 9 – results are shown from Jan – September for current and previous year.
- 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.
Thanks Guneet, this is of great help in doing native HANA scenarios using HANA Live models.
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?
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.
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.
Nice job, Thanks a lot.
Great Help Thank you.
Good job; thank you very much.
Nice blog - thanks!
Guys - also refer to:
Implementation of WTD, MTD, YTD in HANA using Script based Calc View calling Graphical Calc view
Nice blog. Good job. Thanks Guneet!
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.
Anyone can help me out with the QTD?
How can I add in the quarter to the time dimension file?
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:
Nice blog. Is it possible to display month and YTD in the same calculation view for a given month as input ? It looks like this is only showing YTD/PY figures .
Thank you for your comment. Yes, you can modify the calc view to add input parameters. So you can create multiple input parameters (one for month and one for year) and you can then embed them in calc columns such as <$$INPUT_PARAMETER$$>. Instead of CURRENT_YEAR then you can replace them with INPUT PARAMETERS. The view will call the calc column to prompt user to enter the values.