Blitz Unboxing “TODAY” function in Advanced Formulas in SAP Analytics Cloud
New feature SAP Analytics Cloud release: 2020.04 Blog based on SAP Analytics Cloud release: 2020.05
SAP Analytics Cloud Advanced Formulas add huge flexibility and extendibility to the standard SAP Analytics Cloud planning functionalities. I have been asked in the past about the “current date” feature, which was missed by many consultants working on planning scenarios of this fine granularity.
One recent small but very helpful increment to the scripting library is a function which returns the current Coordinated Universal Time in Advanced Formulas.
With this Blitz Unboxing blog, I am trying out this new feature and showing you a simplest example of how this can be integrated in a real-life scenario.
I have created a model which works as a diet tracker. On the screenshot below there are several calculated measures which indicated the energy intake of an end user from his reporting of daily meals.
Using the New Feature
As the name of the blog goes, I put in some random meals for the protagonist “der Vater”. Since this data is based on actual meals, the data in the table below is posted on the “Actual” version. The view below is of current date of “2020-02-26”.
The use case becomes more interesting when the energy consumption forecast for the future can be included into the story.
I added this based on a simple copy into the version “My Forecast” from “Actual”. The formula below uses the brand new “Today()” function. By adding 8 to the CONFIG.TIME_ZONE_OFFSET setting vector, I am telling SAP Analytics Cloud to calculate the date based on the time zone of Shanghai, China. Otherwise it will be the UTC date.
By using the new “Today()” function, I am already on the day granularity as my model is and will no longer need to manually change the time horizon I work on.
These simple three lines are all what I needed to generate the result below.
There are two versions and three dates, the last of which is one day into the future. Note that the view below is an aggregated result on the date dimension, whereas the Data Action created above has copied data on the row level.
This simple test demonstrates the usage of the “Today” function in a simple SAP Analytics Cloud Data Action. The calculation logic however can be arbitrarily complex. Only your own creativity is the limit.
In many business scenarios, you will find the feature useful too, for example filtering the time horizon when you are carrying forward account balances using the “CARRYFORWARD” function.
Summary & End Notes
- The new feature being introduced comes with SAP Analytics Cloud release 2020.04
- The new function “TODAY” returns Coordinated Universal Time (UTC) in the format YYYYMMDD.
Thanks for this example for how to use the TODAY() function.
What are the plans for handling the mapping to local timezones?
Manually having to provide a timezone offset (CONFIG.TIME_ZONE_OFFSET) is not feasible.
Many countries have summer/wintertime switches and the actual timezone definitions change quite often. That's clearly not something every SAC user should have to take care of.
Is there a kind of utctolocal() function available and if so, how/when are the timezone definitions in SAC maintained?
Thanks for your question, Lars.
At this point in time, the timezone is indeed controlled directly in the Data Action.
I agree that for many customers a central place to designate the time zone can come handy, though the current solution does provide full flexibility.
I have communicated your feedback for internal awareness in product management. I would also encourage you to raise such enhancement request directly in context of customer projects to the SAP team.
Thanks for the update!
Any idea when date planning would be possible (adjust dates as a key figure or as master data for end users?)
thanks for your question.
If I understood your inquiry, you would be asking for an SAP Analytics Cloud measure of Date type. In that case, we are currently working on the new model capabilities which addresses this requirement. According to the public product road map, the new model capability is currently planned to come out in Q2 2020.
Does that answer your question?
Hello, Thnak sfor your blog.
I tried to apply your script to my data but I wasn't abe to find a solution. In your example, you are copying the actual values to the plan value. In case I only want to have the right visualization per my time zone could you suggest me the formula?
This is my starting point
CONFIG.TIME_ZONE_OFFSET = +23
CONFIG.TIME_HIERARCHY = CALENDARYEAR
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF
CONFIG.GENERATE_UNBOOKED_DATA = OFF
And this are my data
I would like to change visualization of the date only for Italy (considering a local time +23)
Thank you in advance