Technical Articles
Converting Signed data into Date for Calculations Using a Table Function
To convert signed data into date format and performing calculations (based on Dates such as End of Month, Start of Month, Number of weeks, etc.), Also creating a master table for time periods. This calculation on dates can be used in calculating Labor costs (wages, bonuses, commission, vacation expenses, headcount, etc.) for headcount management.
Sometimes in BPC, we end up storing Dates in the sign data fields. Here I am using AMDP to perform planning calculations and inside AMDP I am calling table function. Ex: In the case of Headcount Planning, I am storing Employees start & end dates as sign data. In that case, the dates are stored as numbers instead of Dates. This calculation on dates can be used in calculating the cost to the company, Headcount Planning, Capex Planning, etc. The signed data is either a Capex cutover date, end of the month, start date, or termination date of any employee stored in the form of transaction data. The below tables give some examples of how the dates will be stored in BPC models.
The below is an input form screenshot, where I am entering the employee’s start date, termination date, annual salary. Based on this input I am calculating wages of the employee such that I am checking if the employee’s start date is less than or equal to End of Month (EOM) and termination date is greater than or equal to End of Month (EOM) then calculate monthly wages.
Input Form
When a user made an input in the above input form the data is stored in a BPC info cube, it will look like below screenshot.
SAP BPC Info Cube Data
In the BPC info cube, all the data is stored into a sign data field that only stores numerical values, so the numerical values corresponding to the inputted dates are stored into its corresponding accounts. For calculating wages and other costs to the company component for the employee, I had used AMDP to perform all these calculations. To achieve this, I had used an SAP HANA table function to calculate/convert these sign data dates into date format. Also, End of Month (EOM) is a property I had maintained in the Time dimension and has been fetched into table function.
Here is an example showing when a date is in the form of signed data then that signed data is equal to date in a calendar such as:
Date in Sign data/Numeric form
To calculate wages and cost to company-related components for employees, I need time periods into which data will be stored for them. So, to derive that I had created a table that will contain all time-related fields used for my calculations. This table is the output of the table function I had created and shown in below steps. It requires TIMEID (or a month from which values to calculate for forecast). The column TIME & TIMEID are coming from time dimension master data as shown in the above tables and the highlighted columns are calculated columns calculated inside table function. Here I am considering calendar type months as 4-4-5, 4-5-4, or 5-4-4. The master table of time periods looks like this:
Time Dimension Master data table:
Time Dimension Master Data
To convert signed data into Date using Table function I had followed the below steps;
- The first step is to fetch or determine signed data to convert into date and the time id from which we will calculate master table fields. Define a table function that will return the master table for time periods as follows:
FUNCTION "<System>"."<Package>::<Table Function>" (TIMEID VARCHAR(32))
RETURNS TABLE (TIME VARCHAR(32),
TIMEID VARCHAR(32),
PREVIOUSTIMEID VARCHAR(32),
STARTOFMONTH VARCHAR(32),
ENDOFMONTH VARCHAR(32),
NUMBEROFDAYS VARCHAR(32),
NUMBEROFWEEKS VARCHAR(32))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
-------code explained in subsequent steps go here-------
<insert your code here>
RETURN
:LT_DATE;
END;- The End of the Month is a property stored in the Time dimension master data in number format or as a signed data. Here we will fetch time for entered time period or time id, so to convert signed data or number into Date I had used the below code, I am fetching 18 months to calculate:
Date_time = SELECT
TIME,
TIMEID,
PREVIOUSTIMEID,
STARTOFMONTH,
ENDOFMONTH,
FROM
<Time Master Data Table>
WHERE
TIMEID = < Entered Time ID> AND
TIMELEVEL = ‘MONTH’
LIMIT 19;- Here in ‘PREVIOUSTIMEID’, I am storing prior months’ time id so to calculate is, we have two cases
When time id is like ‘—-0100’ then subtracting time id by 8900, otherwise
Subtracting by 100.
CASE WHEN TIMEID LIKE '____0100'
THEN (TIMEID - 8900)
ELSE (TIMEID - 100)
END AS PREVIOUSTIMEID- To get ‘ENDOFMONTH’ I have signed data corresponding to it, so we are converting to date in ‘MM/DD/YYYY’ Format as follows:
TO_CHAR (ADD_DAYS (TO_DATE ('1900/01/01', 'YYYY/MM/DD'),
CAST (<ENDOFMONTH Signed data> AS numeric)-2), 'MM/DD/YYYY')- Getting ‘STARTOFMONTH’ as adding 1 to End of month.
TO_CHAR (ADD_DAYS (ENDOFMONTH, 1), 'MM/DD/YYYY')Date_time looks like this:
Date_time Table
This is not useful as the Start of Month is coming as one day ahead of the End of month but start of month for 2020.OCT is the start of the month for 2020.NOV. so, I need to map 2020.OCT start of the month to 2020.NOV start of the month which can be done using TIMEID.
- Now self joining table Date_time and getting Start of Month, Number of Days as follows:
LT_DATE = SELECT
Date1.TIME AS TIME,
Date1.TIMEID AS TIMEID,
Date1.PREVIOUSTIMEID AS PREVIOUSTIMEID,
Date2.STARTOFMONTH AS STARTOFMONTH,
Date1.ENDOFMONTH AS ENDOFMONTH,
Date1.NUMBEROFDAYS AS NUMBEROFDAYS,
Date1.NUMBEROFWEEKS AS NUMBEROFWEEKS
FROM
:Date_time AS Date1
INNER JOIN
:Date_time AS Date2
ON
Date2.TIMEID = Date1.PREVIOUSTIMEID;
RETURN
:LT_DATE;- “NUMBEROFDAYS” can be calculated as:
DAYS_BETWEEN (TO_DATE(STARTOFMONTH,'MM/DD/YYYY'),
TO_DATE(EOM,'MM/DD/YYYY')) + 1- “NUMBEROFWEEKS” can be calculated as:
(DAYS_BETWEEN (TO_DATE(STARTOFMONTH,'MM/DD/YYYY'),
TO_DATE(EOM,'MM/DD/YYYY')) + 1) / 7
Now the table looks like this:
Final Output
This table function can be called in a calculation view or an AMDP to calculate labor costs (such as wages, monthly or weekly salary, bonus, vacation expenses, headcount) based on the month days or weeks and start of month or end of the month.
It is a very common requirement that to calculate the cost of the company and capital expenditure based on the date. While it is possible that you can directly perform these calculations in input forms, the input form is facing a performance issue in my case as the number of columns and rows are very much. In my opinion, we can make better use of AMDP and SAP HANA capabilities to perform these calculations very smoothly and quickly. I have tried and tested this method to make better use of AMDP and SAP HANA capabilities to enable planning calculations performed in a quick & easier way.
In this post, we have learned about how to create a table function that will help to simplify date-based calculations for headcount, Capex and cost to the company planning to be used in an AMDP or a Hana Calculation view. Feel free to provide your valuable feedback and welcoming question and suggestions for this article.
for more questions regarding the blog post please go through the below link:
https://answers.sap.com/questions/ask.html
References :
Table Functions in SAP HANA blog post– step by step guide:
https://blogs.sap.com/2018/11/05/table-functions-in-sap-hana-step-by-step-guide/
Sample Code:
FUNCTION "<System>"."<Package>::<Table Function>" (TIMEID VARCHAR(32))
RETURNS TABLE (TIME VARCHAR(32),
TIMEID VARCHAR(32),
PREVIOUSTIMEID VARCHAR(32),
STARTOFMONTH VARCHAR(32),
ENDOFMONTH VARCHAR(32),
NUMBEROFDAYS VARCHAR(32),
NUMBEROFWEEKS VARCHAR(32))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
LT_DATE = WITH DATE_TIME AS
( SELECT
TIME,
TIMEID,
PREVIOUSTIMEID,
TO_CHAR(ADD_DAYS(ENDOFMONTH, 1), 'MM/DD/YYYY') AS STARTOFMONTH,
TO_CHAR(ENDOFMONTH, 'MM/DD/YYYY') AS ENDOFMONTH,
FROM ( SELECT
<Time period> AS TIME,
<Time id> AS TIMEID,
CASE
WHEN <Time id> LIKE '____0100'
THEN ( <Time id> - 8900)
ELSE ( <Time id> - 100)
END AS PREVIOUSTIMEID,
TO_CHAR(ADD_DAYS(TO_DATE('1900/01/01','YYYY/MM/DD'),
CAST(<End of month> AS numeric)-2),
'YYYY/MM/DD') AS ENDOFMONTH
FROM
<TIME MASTER DATA TABLE> AS MD
WHERE
<TIME LEVEL> = 'MONTH' AND
<TIME TIMEID> >= TIMEID (Input Parameter)
ORDER BY <TIME TIMEID> ASC
LIMIT 19)
)
SELECT
DATE1.TIME,
DATE1.TIMEID,
DATE1.PREVIOUSTIMEID,
DATE2.STARTOFMONTH,
DATE1.ENDOFMONTH,
(DAYS_BETWEEN (TO_DATE(DATE2.STARTOFMONTH,'MM/DD/YYYY'),
TO_DATE(DATE1.ENDOFMONTH,'MM/DD/YYYY')) + 1) AS NUMBEROFDAYS,
(DAYS_BETWEEN (TO_DATE(DATE2.STARTOFMONTH,'MM/DD/YYYY'),
TO_DATE(DATE1.ENDOFMONTH,'MM/DD/YYYY')) + 1) / 7 AS NUMBEROFWEEKS
FROM
DATE_TIME AS DATE1
INNER JOIN
DATE_TIME AS DATE2
ON
DATE2.TIMEID = DATE1.PREV_TIMEID;
RETURN
:LT_DATE;
END;