Community
Data and Analytics Blog Posts
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Problem definition:

In the area of reporting, for example in aging Reports there is always a requirement to calculate days between 2 dates like posting date and key date or order date and delivery date etc. In SAP Analytics Cloud generally this leads to a story defined calculation of type Date Difference. This calculated Measure definition supports the following options.

  • Granularity - This is the granularity of the date difference calculation. Supported values are Year, Month, Date, Hour, seconds, minutes.
  • Time A and Time B -  These are the 2 dates of which we want to calculate the difference between. They support Current Date, Current Timestamp and Date type dimensions of the model.
  • Dimension Context - This is the dimension context under which the difference is calculated.
  • Result Aggregation - How the difference should be aggregated. You can keep it to AVERAGE or change as per requirement.

This calculation works perfectly if you want to calculate difference between 2 existing date type dimensions, or one date type dimensions and current date/time. The calculation does not support any Input control for dimensions and hence there is no option to calculate date difference between a date dimension and a user selected key date.

Technical Solution:

We will try to build a workaround solution to calculate date difference between user selected date and Date dimension which can help us create aging report with key date as selection.

In SAC stories calculation editor, date difference calculation is done by defining a calculated measure of type Date difference and below is how the setup looks like.

N1kh1l_0-1778530163467.png

The only supported option for 2 dates are Current Date, Current Timestamp and Date dimension of the model. No option for any input control selection.

We want to calculate date difference between Date dimension and user selected date.

  • Define User defined key Date selection

To capture the user selection for key date we will define a Date Input control of type range and will use custom calendar. This will give us a calendar picker to select date. 

N1kh1l_1-1778530615807.png

The resultant date input control can be hidden from outline view and also disconnected from any widgets by keeping the below setting for  the Input control Linked Analysis. Its just a place holder input control with no filtering affect on any Tables or widgets.

N1kh1l_3-1778530806148.png

The above will help me get the below for key date selection. ( still waiting for SAC to provide calendar pickers to select dates ( From/ To and date ranges, ....topic for another day)

N1kh1l_2-1778530675340.png

Now we have created a mechanism to allow users to select key date. Our next task is to pass this key date selection to our calculation.

The date difference calculation only supports dimensions of type date. Lets build up a date dimension with our user selected key date. 

SAC supports custom dimensions. The issue is custom dimensions also do not support input controls or variables. Lets workaround this problem too.

  • Define a Script variable to be used in calculated measures

Define a script variable of type integer as shown below. This variable will store the integer pattern for selected date. If user selects 15 March 2024, it should store 15042024. We will define the logic to extract this in subsequent steps.

N1kh1l_4-1778531653281.pngN1kh1l_5-1778531676924.png

  • Define a Text Box and assign the dynamic text binding to the selected Key date

This will help us to read the selected key date and extract the integer pattern from it. 

N1kh1l_6-1778532046464.png

Output: The user selection is assigned as shown below.

N1kh1l_7-1778532092918.png

  • Define a logic to extract integer date pattern from user selected date (Mar 15, 2024)

 we will use some java scripting to derive the formatted date from above. We can assign the below code to a button which will help us calculate the value and also refresh the application.

N1kh1l_8-1778532368177.png

// 1. Read selected Key Date

var seldate=Text_1.getPlainText(); // Text Box value with dynamic text binding

var monthName = seldate.substr(0, 3);
var day = seldate.substr(4, 2);
var year = seldate.substr(8, 4);

// 2. Map month name to number
var monthNum = "";
if (monthName === "Jan") { monthNum = "01"; }
else if (monthName === "Feb") { monthNum = "02"; }
else if (monthName === "Mar") { monthNum = "03"; }
else if (monthName === "Apr") { monthNum = "04"; }
else if (monthName === "May") { monthNum = "05"; }
else if (monthName === "Jun") { monthNum = "06"; }
else if (monthName === "Jul") { monthNum = "07"; }
else if (monthName === "Aug") { monthNum = "08"; }
else if (monthName === "Sep") { monthNum = "09"; }
else if (monthName === "Oct") { monthNum = "10"; }
else if (monthName === "Nov") { monthNum = "11"; }
else if (monthName === "Dec") { monthNum = "12"; }

// 3. Reorder to DDMMYYYY
var formattedDate = day + monthNum + year;

console.log(seldate);
console.log(formattedDate);

SV_KeyDate=ConvertUtils.stringToInteger(formattedDate); // Script variable assignment

console.log(SV_KeyDate);

Application.refreshData(); //Trigger Refresh after assigning value to variable

 

  • Create a calculated Measure using the Script variable define earlier (SV_KeyDate).

Use @ to read the script variable

N1kh1l_9-1778532628206.png

  •  Create a calculated Dimension of type Measure based dimension using the above calculated Measure

Configure to use measure values as dimension members. Keep decimal as 0.

N1kh1l_10-1778532781214.png

  • Define another calculated dimension using the previous calculated dimension.

N1kh1l_11-1778532886603.png

TODATE(SUBSTRING([d/"MBD_KD"].[p/ID],0,2)+"-"+SUBSTRING([d/"MBD_KD"].[p/ID],2,2)+"-"+SUBSTRING([d/"MBD_KD"].[p/ID],4,4),"dd-mm-yyyy")

So, our scripting will assign 15032024 to script variable SV_KeyDate. Calculated Measure will be assigned this value. Next our first calculated dimension will get "15032024" as member id. Next we use TODATE() ( Syntax: ToDate("date_string";"input_format") ) function to create a date member "15-03-2024" using "15032024". The above formula creates the date member in correct format.

  • Define the Final Calculated Measure for Date difference

 Now the calculation editor allows us to select the previous defined calculated dimension as one of the date selections in Date difference calculation.

 

N1kh1l_12-1778533351261.png

  • Final Result

N1kh1l_13-1778533504182.pngN1kh1l_14-1778533560154.png

Excel calculation of Days Difference

 key date
Date15/03/202410/05/2024
01/01/202474130
04/01/202471127
06/01/202469125
07/01/202468124
10/01/202465121
03/02/20244197
05/02/20243995
08/02/20243692
11/02/20243389

Limitations: 

Currently The story does not refresh automatically on change in Key Date selections. Button interaction has been leveraged to calculate the dimensions and the measures used in the calculation and also trigger the table refresh. This part could be improved with a scripting workaround.

Summary:

This way we can achieve calculating date difference calculation between a standard date dimension value and user defined date selection. 
If you think this post has helped you, please like and share your thoughts in the comments section below. Incase of any queries please ask the same in the comments section and I would be happy to answer the same.

Thanks for reading. I hope you enjoyed the blog.

Check the SAP Analytics Cloud topic page for more up to date information https://community.sap.com/topics/cloud-analytics

follow and read other informative blog posts on SAC browse the below link

https://blogs.sap.com/tags/67838200100800006884/

 

Regards

Nikhil

 

 

 

 

 

Comment
2 Comments