Date Calculations in SAP Analytics Cloud using Custom Date

[former_member401717]

Introduction

SAC provides a wide variety of Date based calculations already available like Current Year, Current Quarter, Current Month in the current period and YTD, QTD & MTD in To date we are also provided with a function to shift previous value or Previous Period, Previous Year, Previous Quarter & Previous Month. But all these calculations happen concerning the System time. We often come across situations where users want to run for a custom date input and not the system time which is today. This can also be achieved in SAC by following the below steps

 

Date Based Calculations

For the blog post, We will consider a simple data set that uses a single measure for Net revenue and a single dimension for time which contains data for the years 2017 & 2018.

 

 

To get Date based Calculations we just need to set up a calculation of type restricted measure and enable the Selection context for the Time Dimensions

 

 

For the above example, I will choose Year from the Current Period Navigation,

 



Since we don’t have any data for the year 2020(System Date) the restricted measure column does not return any value

 

Now let's try setting a custom date like 2018 for which we have data available to get the current period value.

 

Let us create another calculation of type restricted measure and enable the selection context for the time dimension. But this time instead of selecting from the navigation list create a Calculation Input control and in Input values choose Select by Range

 

In the Set Date Range for Time Dialog change the current date from System Date / Period to Create Current date input control

 

A Story filter will be created for the current date where you will be allowed to change the date as per your preference or use the current system date to act as a base for your calculation. Selecting a date in 2018 will provide the current year value of 2018 based on the granularity setting.

 

 

We can also use an offset to define the offset direction to Look back or Look ahead and set custom ranges based on the defined granularity.

 

Conclusion

This Blogpost shows you how to use the predefined date/time filters that are available and how you can define a custom date as “Today” and an offset utilizing the flexible date/time-based calculations in SAP Analytics Cloud

 

 

[former_member186338]

What about retail calendar support based on weeks (months have fixed number of weeks like 5-4-4)?

[former_member744134]

Thanks for this, its really helpful. With this MTD figures comes perfect by selecting year and month filter. (custom Jan 2013 current date). I have 2013 and 2014 daily sales data, I took month as granularity.

How to get YTD based on filter selection in the same table...

Thanks
Arvind

[former_member750781]

Unfortunately this option does not work for the Application Designer!

[guejul]

Is there any workaround? I would really need this functunality in App Designer!

[former_member775357]

How can i used current year function in Model level ? If it is possible, can you please send it detailed.

 

[Sanja]

Can you create two range date input controls to be selectable by the user on the screen and to be usable in restricting the measures?

[nsatishreddy21]

I too would like to have similar functionality in App designer. SAP doesn't seem to have measure input control for restricted measures in SAC App.

[yamit]

I think that is not possible,

I found this :

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/e1940498b0f3482892916...

[a_rubio1]

Hello thanks for yor post. that's very useful.

 

WARNING FOR DEVELOPERS

 

If you change the setting related to time dimension from SYSTEM MANAGE to USER MANAGE you cannot go back. it cannot be reverted.   the main problem is that the DATA ANALYZER of your data model will stop working for USER MANAGE Time dimension. you will get an error message like this    Data Analyser mode is not supported for a model with custom date dimensions..

in the end you will have to rebuild your model FROM SCRATCH if you want to go back to SYSTEM MANAGE TIME DIMENSION.

 

Unless someone from the SAP Community tells me otherwise, the question would be

IS THIS A GOOD TOOL FOR DEVELOPERS ???

 

Happy 2024 !!!

ENRIQUE HERNANDEZ QUEVEDO