Time Distribution function in Datasphere

[JulienM]

Hello the community! 

Do you remember remember the Time Distribution function in SAP BW ? 

That idea when you receive a key figure at a calendar week level for instance, and you wanted to split it at the calendar day level and the system was doing it wonderfully by just the use of that function. We do not want to simply divide by 7 because of the decimals and rounding.

Do you have any idea how we would reproduce that with SAP Datasphere ? Do we have to go with Python script?

Any idea is welcomed. Always good to see how you would approach that scenario.

Thanks 

Julien

[JulienM · Accepted solution]

Hi everyone,

It seems by just diving by the amount of days in a week and then casting that number in a decimals(18,2) for instance, when we aggregate the value it's well showing the initial value without rounding issues.

We are well doing a join with the time dimension in order to split the value by date.

Thanks

[TuncayKaraca · Accepted solution]

Hi Julien, thanks for the update.

[XaviPolo]

Hi @JulienM 

There is no function like that in DSP.

But if you need to distribute a value from "WEEK" to "DATE" you can do it with a SQL Script view.

You can use the Time Dimension of DSP (Create Time Data and Dimensions | SAP Help Portal) that will give you a "Time Dimension - Day" view with the Date (SQL and SAP format) and the Week (ISOWEEK if you generated it after v2023.08) in YYYYWW format like "202401"

This view will allow you to calculate the number of days per CALWEEK and to join with your data to get the dates rows, with something similar to:

num_days = SELECT CALWEEK, COUNT(DATE_SQL) AS NUM_DAYS
FROM DTC."SAP.TIME.VIEW_DIMENSION_DAY"
GROUP BY CALWEEK;

dates = SELECT DATE_SQL, CALWEEK 
FROM DTC."SAP.TIME.VIEW_DIMENSION_DAY";

RETURN 
	SELECT 
		YD.CALWEEK, DATES.DATE_SQL, YD.AMOUNT / NUMDAYS.NUM_DAYS AS DAY_AMOUNT
	FROM <YOUR_DATA> YD 
		JOIN :dates DATES ON YD.CALWEEK = DATES.CALWEEK
		JOIN :num_days NUMDAYS ON YD.CALWEEK = NUMDAYS.CALWEEK
;

Regards,

[TuncayKaraca]

Hi @JulienM,

Are you using Time Data (Time Tables and Dimensions) generated in the Space? That should make the distribution based on Timestamp (Date).

Otherwise pretty much if a function needed, options would be (1) Python Script (2) SQL Script.

Regards,
Tuncay