Skip to Content
Technical Articles
Author's profile photo ravi akasapu

Working with Dynamic Time Navigation Functions in Sap Analytics Cloud

 

Working with Dynamic Time Navigation Functions in Sap Analytics Cloud

This document is a step by step guide on how to work with Dynamic Time Navigation Functions. Please read this in conjunction with SAP Help Documentation.

When working with Calculated Measure, there might be use cases that require Lookup along with various time functions. Some of the examples like Year Total values, Rolling sum totals etc. We  can achieve these type of calculations with the use of LOOKUP or RESTRICT along with Dynamic Time Navigation Functions.

I will try to outline the steps in creating such measures using calculations to get total value in a year using Lookup function.

New Calculated Measure in Model

Create a new Calculated Measure in the model. The formula has Time function — Current(“Year”) that refers to current year. Please refer to SAP Help documentation for more functions like previous, next etc…

LOOKUP([VALUE],[d/Date].[p/YEAR]= Current("Year"))

Alternatively the formula can also be created by pressing CTRL + Space after the Date as below and choose Set Dynamic Time Filter.

Dynamic%20Time%20Navigation%20Functions

Dynamic Time Navigation Functions

 

This will open a new window on Dynamic Time selections for the Calculated Measure.

I have adjusted the result to check the Year to DATE property instead of Dimension directly.

selections%20for%20TIME

selections for TIME

 

 

Aggregations

When using the dynamic time functions, it is also important to use Required Dimensions in Aggregations. Choose Date dimensions for aggregation

 

Date Filter (Optional)

There is an optional filter that can be enabled within the story filter. Depending on the TIME filter and variables, this can be used in stories.

Result

 

Some more useful calculations

  • Rolling sum or Look back totals
LOOKUP([VALUE],[d/Date].[p/YEAR]= LastPeriods("Month",12))
  • Aggregation with single version per year
LOOKUP([VALUE],[d/Date].[p/YEAR]= Current("Year")AND [d/Version]="public.Budget")
  • Balance for rest of year
ITERATE(PRIOR()-[VALUE],[YEAR_TOTAL], [d/Date].[p/CALMONTH])

 

Thanks

Ravi

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.