New Time function "Full Year Value"

Hi all,

I have been looking for easy ways to manage this, but so far no satisfying solution.

Requirement:

In a given dataview, where the screen has a month selection (user is supposed to enter actual values for this month), I need to display as a comparison :

the year to date budget value (easy)
the year to date actual value (easy)
the full year budget value => not so easy !

Current workaround

The current workaround is a fixed ReferTo on the december month for the current year, + a yearly cumulated value.

Other tested solution : feed a "current year" flag and do a referto on this flag, but this does not work as the selection intersects the screen selection and shows only one month.

Issue

There is no way to make this dynamic and follow the month selection !

Idea

Add a "Full Year Value" Time function that will provide for this block the total value of all 12 months of the year currently selected.

Find more posts tagged with

Accepted

Status: Accepted

Comments

Alex Needham

I was just coming to add this as a recommendation.

Bump.

Leone Scaburri

Hi @Etienne CAUSSE,

I voted your idea. I'd like to suggest you a workaround to use. You can introduce a "twin" cube as a copy of the original one but dimensioned by YEAR. Using such a cube you can always show the whole year value even if a single period is selected.

The advantage compared to the option you mentioned is a more dynamic approach avoiding any hardcoded refer to, and definitely better performance. On the other side it requires data duplication in a new cube.

Etienne CAUSSE

Hi Leone, and thanks for your comment.

I'm aware of the workaround possibility. However as you said two drawbacks:

data duplication, in some cases we have very big cubes so it's a real issue
data synchronization, as any data entry would require a dataflow to the "year" cube to be updated.

The FY value would avoid both these issues and also simplify the architecture (if I have 30 cubes in this situation I don't want to add 30 twin cubes...).

Etienne

Nicolas BENDONGUE

Hi @Etienne CAUSSE ,

did you try the below solution?

Create a custom time entity →i.e. Month of Year with the following members January to December

2. Create a Custom relationship Month > Month of Year. i.e. 202312 > December. 202412 > December. You can feed the relationship by using an extract/reload logic for instance. This is a one time admin task. This task need to be performed when adding more years in the time range.

3. In the layout fo your dataview, on the block which should represent the Full year (i.e. budget), add a “Refer to” with member value = December of the your custom time entity (Month of Year) + the time function = “Yearly Cumulated Value”

Then, as your example, assume the selected month in the screen is 202303, so the selected year will be 2023 (thanks to the relationship), so the block will show Yearly Cumulated Value of the Year 2023 as of the month of December.

I think this should meet your requirement in a more dynamic way. If the month selection is 202401, so the selected year will be 2024. Then the value of the block will show Yearly Cumulated Value of the Year 2024 as of the month to December.

This custom time entity can be usefull for other purposes.

Another solution could be to use the “Apply selection to block” action. But with this solution, you need to have a logic to flag YYYY12 of the current data entry year (admin task) and then implement an open trigger procedure to pass the flag as a “Refer to” thanks to the “Apply selection to block”.

Let us know your thoughs!

Regards, Nicolas

Etienne CAUSSE

Hi @Nicolas BENDONGUE and thanks for the input.
I'm aware of the technique as we used it in other contexts, but on the application where we needed it originally, it was not working in that specific Board version (before 10.7 I think).
I'll need to test again in new versions.

Nicolas BENDONGUE

@Etienne CAUSSE, for your reference, I am using Board V12.

Regards,

Product Management Team

Hi Etienne, thank you for sharing your idea! We appreciate the time and effort you put into crafting your suggestion, and we understand the need of this solution. We have accepted this Idea to be part of our backlog and we are evaluating the feasibility of adding it to the development roadmap.

We are committed to delivering the best possible experience to our customers, and we must prioritize the features and enhancements that will have the most significant impact on your daily use of our software.