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
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
What about retail calendar support based on weeks (months have fixed number of weeks like 5-4-4)?
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...
Unfortunately this option does not work for the Application Designer!
Is there any workaround? I would really need this functunality in App Designer!
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.
How can i used current year function in Model level ? If it is possible, can you please send it detailed.
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?