###
**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