Date Calculations in SAP Analytics Cloud using Custom Date
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.
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