How to use period by period recursion

Center of Excellence

1. Abstract

This article will highlight the advantages of seamlessly incorporating a native functionality, like the “Period by Period recursion” function, within a business context. It illustrates how this integration simplifies tasks, boosts scalability, and optimizes maintenance, providing practical insights for its effective utilization. 

2. Context

This document explores the “Period by period recursion” built-in function, spotlighting its practical application through a cash flow calculation example. In many projects, leveraging a value from the previous period as a starting point for the current one is a common challenge. 

This functionality allows to use the result of the calculation on the first period as the source for the calculation on the next period. This option is particularly useful for those calculations where the result of the Dataflow for the first period needs to be used as the source for the following one like in cash flow calculation. 

3. Content

In this use case, we are leveraging the function period by period recursion from our platform. This scenario highlights not only the core function but also underscores the necessary settings and requirements associated with it. 

Within this section, we’ll introduce entities, cubes, settings and crucial configurations. The guidance provided will walk you through establishing the procedure and configuring the data flow, emphasizing the how-to use of the “Period by period recursion” function. 

3.1 Entities, Cubes and Rules 

In our demo data model, we possess two entity trees: the main one representing the cash flow item and the second focusing on cities. 

As the calculation takes place within the cash flow item entity, we’ve established a rule specifically for that entity (see below). This rule serves dual purposes, being utilized in the frontend for on-the-fly calculations and in the backend for additional calculations. The member “Impact on Beginning” will be calculated in a separate group within the procedure. 

The Cash Flow cube (type: double) holds data that will be utilized to showcase the “period by period” function. The additional cubes shown in the screenshot pertain to various administration settings, such as front-end data entry restrictions, and so forth.  

3.2 Initial Data View

In the frontend, the data view on the left facilitates users in entering data for diverse cash flow items across various periods. Furthermore, users can utilize the second table to assign a default percentage on the cash flow item Beginning. Subsequently, the calculated value at runtime will be stored as an absolute value at the end of the procedure.

In the below illustration we see in detail how the calculation occurs based on the period offset and the current month. We observe that, in the current month, values from the previous month are accessed. Summations are formed, multiplications take place, and on-the-fly calculations are carried out. The value of the item ct on Beginning is not written physically in the procedure group Period by Period. This process occurs with the last group Absolute Impact, where the rule (figure 2) gets applied as well.

3.3 Procedure configuration

The procedure for the calculation of Cash Flow will contains following steps:

• Select Cash Flow line = Sales
• Dataflow TMP Sales = Cash Flow (Rule applied)
• Select Cash Flow line = Expenses
• Dataflow TMP Sales = Cash Flow (Rule applied)
• Select Cash Flow line = Beginning
• Dataflow Cash Flow Beginning calculation (see next chapter)

3.4 Dataflow step setup

To be able to use the function “Period by Period recursion” the source and target cube must be the same. In between we can use different cubes to enrich with further data or consider different use cases, like with the data block “d = Default”. Furthermore, we need to ensure to define a period offset of -1 on the source cube (data block – time functions).

The expression, where we define our target e: Cash Flow calculates the beginning (Cash) of the current period based on the previous period Beginning, adding the expenses and sales and applying the default percentage on the beginning value.

3.5 Calculation domain

The calculation domain must be configured since in our scenario both temporary cubes and physical cubes lack the Cash Flow Item entity. Consequently, it’s necessary to extend the tuple on entity Cash Flow Item. The screenshot below shows that in detail.

3.6 Result

When you run the calculation, the result, as shown below, highlights the properly calculation. For instance, the start of February 2023 matches exactly with the end of January 2023.

Furthermore, you can identify that the percentages from data view Default % are applied on the latest calculated Beginning. Within the screen there is a active selection on the entity city.

Excurse: Why do we store the physical value of the default percentage? Since we can’t aggregate percentages (this example is on one city) it is important to store the physical value in the cube and recalculate the percentage, so that in an aggregated view you still get logical values.

4. Conclusion

The “Period by period recursion” native function is a robust tool with high-performance capabilities and efficient calculations that could replace complex looping procedures built based on old logics. Its dynamic calculation approaches boost adaptability, and its simplicity ensures easy maintenance and scalability. Overall, it’s a strong asset for efficient and streamline business operations.

Center of Excellence

Thanks @Atilla Özbey for the insights on this article!

Gustavo Camacho

It doesn't work with Prevoius Year-setting. You must set period offset -12.

Mayby this new function isn't working with all existing functions

Pontus Andersson Frid

Does this have to be used combined with offset function?

Or can I run a heavy DF selected for many months making the dataflow work month by month instead of working with all month´s tuples?

Leone Scaburri

"If the target Cube of a Dataflow is also a source for that same Dataflow and the source block is set to period offset negative, a new option can be selected."
The option can only be selected if all the previously mentioned requirements are met.