SAC: Do we need to define the properties of a dimension as text or integer to use it as a date?

[skcpaleti65]

Hi Experts,

In SAP SAC, how we need to define the properties of a dimension, i.e., as text or integer to use it as a date, e.g., as shown below to be used in the commission calculation:

EmpID       start_date     sales_start_date

Emp1        02152021    05012021

Emp2        09052022     10012022

Emp3       10092023      01052024

We have the below scenario for commission calculation:

Each Employee has a start_date and sales_start_date

Every month, on any given day, when a Data Action is run, it has to calculate Today - Sales_start_date. 

If it is over 2 years and above, the commission is 2.5% of the Net Sales by that sales person, 

If it is less than 2 years but greater than or above 1 year, it's 1.75% of the net sales. 

It it is less than 1 year, it's a fixed value of $1000.

How to achieve this and with what data type, the start_date and sales_start_date properties of EmpID dimension have to be defined as, as there is no 'date' data type in SAC.

Thanks,

SP

[N1kh1l · Accepted solution]

@skcpaleti65 

The type should be TEXT and the format should be as one of the below

YYYY-MM-DD, YYYYMMDD, YYYY/MM/DD, YYYY.MM.DD

Finally you should construct your logic something like below

IF DATEDIFF(TODAY(), [EmpID].[d/Sales_start_date], "YEAR") > 2 THEN
DATA()=RESULTLOOKUP() // Adjust as per your calculation logic
ENDIF

Hope this helps !!

Nikhil

[skcpaleti65 · Accepted solution]

Thanks @N1kh1l