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

[Cynthia_lalanne]

Hi @skcpaleti65 , 

You need to create a propertie of type Text to use it in data action as a date. 

In my case I use this properties to calculate depreciation. So, in my data action, I can use DATEDIFF function between two dates or DAY function to count number of days in a month from a date in a propertie. 

I use dot between year, month and day but you should succeed without (to test).

I hope its helps you, 

Best Regards, 

Cynthia

Bests Regards, 

Cynthia

[Cynthia_lalanne]

It is also important the format of date in propertie. I tried like you DDMMYYYY but SAC doesn't understand it. You have to use format YYYYMMDD.