Introduction
The purpose of creating this table function is to calculate the key figures which are very important for Target Vs Sales Analytical reporting. This Key figures can be referenced in Predictive Sales reporting.
The functionality of the following four key figures :-
1. Total No of Business/Working days (“Total_WDS”) in a January 2020 is “22”. Please refer the below calendar snapshot. Count the yellow highlighted box which is an Working day as per calendar. And red border highlighted box i.e 23rd Jan,2020 is an Holiday.
Output :
2. Is “Today Working ??” (TODAY_WD”) - The Input Date is a business day or an Holiday. This measure output is boolean (“Y/N”). For e.g input date 23rd Jan, 2020 will return “N” as this is an public Holiday.
output:
3. “Current working days”(“CRNT_WD”) – Returns the Current no of working days. For e.g Input date 20th Jan, 2020, no of working day is “14” . Count the yellow color highlighted box.
Output:
4. “Working Day in %(Percentage)” (WDS_PRCNT) - The formula is
(“CRNT_WD”/”TOT_WDS”)*100
Use Case of the following key Figures:
I have addressed the requirement of Sales business reporting. This above calculated key figures can be referenced in a “Predictive KPI” calculation. For e.g if a Sales Person sold an x no. of material till 7th Business day of the month then how many more no of material can he/she sell in the remaining no of working days.
This requirement was very common in “Target Vs Actual Sales” reporting requirement. So I Shared this code with you all.
This table function can be consumed directly in the SQL code, Stored Procedure or any Calculation Views. In my case i consumed in a calculation view.
Input Parameter: One Input Parameter of Varchar data type of Size 11 (Date format ‘2020-01-20’)
HANA Table Function Code:
FUNCTION TF_XXXX_XXXX_XXXXX (IN “IP_YYYYMMDD” VARCHAR(11))
RETURNS TABLE
(“TOT_WDS” TINYINT,
“CRNT_WD” TINYINT,
“TODAY_WD” VARCHAR(10),
WDS_PRCNT DECIMAL(5,2))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
/*****************************
Write your function logic
*****************************/
T_YEAR VARCHAR(4);
T_MTH VARCHAR(2);
T_DAYS TINYINT;
I SMALLINT;
I_CRNT_DAY NVARCHAR(2);
C_YR VARCHAR(4);
C_MTH VARCHAR (2);
SQL_COL NVARCHAR(256);
T_MNTH_RSLT NVARCHAR(31);
T_WDS TINYINT;
C_WD TINYINT;
TODAY_WD NVARCHAR (1);
TOT_WDS TINYINT;
CRNT_WD TINYINT;
CURSOR CS2
FOR SELECT “MON01”, “MON02”, “MON03”, “MON04”, “MON05”, “MON06”, “MON07”, “MON08”, “MON09”, “MON10”, “MON11”, “MON12”
FROM “XXXXX”.”XXXXX” — You have to use you own Company Leave calendar table
WHERE IDENT = ‘xxxx’ — Your Country code Filter
AND JAHR = T_YEAR; — Year Filter
BEGIN
I_CRNT_DAY := RIGHT(“IP_YYYYMMDD”,2);
C_YR := LEFT(“IP_YYYYMMDD”,4);
C_MTH := SUBSTRING(“IP_YYYYMMDD”,6,2);
T_YEAR := LEFT(“IP_YYYYMMDD”,4);
T_MTH := SUBSTRING(“IP_YYYYMMDD”,6,2);
TODAY_WD := ‘ ‘;
If ((T_MTH = ’01’) OR (T_MTH = ’03’) OR (T_MTH = ’05’) OR (T_MTH = ’07’) OR (T_MTH = ’08’) OR (T_MTH = ’10’) OR (T_MTH = ’12’)) then
T_DAYS := 31;
ELSEIF ((T_MTH = ’02’) OR (T_MTH = ’04’) OR (T_MTH = ’06’) OR (T_MTH = ’09’) OR (T_MTH = ’11’)) then
T_DAYS := 30;
ELSE
TOT_WDS := 0;
CRNT_WD := 0;
TODAY_WD := ‘X’;
END IF;
If “TODAY_WD” = ‘ ‘ then
If (T_MTH = ’02’) then
T_DAYS := 28;
I = MOD(T_YEAR, 4);
if I = 0 then
T_DAYS := 29;
END IF;
END IF;
For R2 AS CS2 DO
If T_MTH = ’01’ Then T_MNTH_RSLT := R2.MON01;
elseif T_MTH = ’01’ Then T_MNTH_RSLT := R2.MON01;
elseif T_MTH = ’02’ Then T_MNTH_RSLT := R2.MON02;
elseif T_MTH = ’03’ Then T_MNTH_RSLT := R2.MON03;
elseif T_MTH = ’04’ Then T_MNTH_RSLT := R2.MON04;
elseif T_MTH = ’05’ Then T_MNTH_RSLT := R2.MON05;
elseif T_MTH = ’06’ Then T_MNTH_RSLT := R2.MON06;
elseif T_MTH = ’07’ Then T_MNTH_RSLT := R2.MON07;
elseif T_MTH = ’08’ Then T_MNTH_RSLT := R2.MON08;
elseif T_MTH = ’09’ Then T_MNTH_RSLT := R2.MON09;
elseif T_MTH = ’10’ Then T_MNTH_RSLT := R2.MON10;
elseif T_MTH = ’11’ Then T_MNTH_RSLT := R2.MON11;
else T_MNTH_RSLT := R2.MON12;
end if;
end for;
T_WDS := 0;
C_WD := 0;
TODAY_WD := ‘N’;
FOR i in 1..T_DAYS do
If SUBSTRING(T_MNTH_RSLT,i,1) = 1 THEN
T_WDS := T_WDS + 1;
if i <= I_CRNT_DAY THEN
C_WD := C_WD + 1;
end if;
if i = I_CRNT_DAY THEN
TODAY_WD := ‘Y’;
end if;
end if;
END FOR;
If T_YEAR < C_YR then
C_WD := T_WDS;
elseIf T_YEAR > C_YR then
C_WD := 0;
elseIF T_MTH < C_MTH then
C_WD := T_WDS;
elseIf T_MTH > C_MTH then
C_WD := 0;
end if;
end if;
“TOT_WDS” := T_WDS;
“CRNT_WD” := C_WD;
“TODAY_WD” := TODAY_WD;
Return
select sum(“TOT_WDS”)as “TOT_WDS”,sum(“CRNT_WD”)as “CRNT_WD” , max(“TODAY_WD”)as “TODAY_WD”,(Sum(“CRNT_WD”)/sum(“TOT_WDS”)*100) as WDS_PRCNT
from(
select “TOT_WDS” as “TOT_WDS” ,0 as “CRNT_WD”, ” “TODAY_WD” from Dummy
union
select 0,”CRNT_WD”,” from Dummy
union
select 0,0,”TODAY_WD” from Dummy);
END;
Screenshot of the Function Output:
◉ Snapshot of Input Parameter Screen
◉ Snapshot of Calculation View Output
No comments:
Post a Comment