Forecast Screen - Show total of an algorithm switching between two cube slices

Sebastian Kappen

Hi,

I'm currently building a forecast screen. The requirements specify the following use case:

1. the user selects a month and yeear
2. months up to (excluding) the selected month show the actual value
3. months including and after the selected year show the reference value (e.g. budget or estimate)
4. In addition, the previous year's actual value should be shown, as well as the reference sum for the selected year
5. In addition, the total forecast for this year should be shown (basically the sum of points 2 and 3)

The following layout already covers requirements 2-4

AUX_YearMonth Boundary by Month_Only is a helper cube, which basically shows if a certain month is before or after the selected Month_only value. So if the user selects July it will yield 0 for 2022-01 to 2022-06 and 1 for 2022-07 to 2022-12.

Now for the last requirement, it should be quite simple: Get the row total of block d. Unfortunately, Board seems to recalculate the algorithm, but only on the highest aggregation level (year). So instead of taking the monthly values produced by the algorithm in block d, it takes the sum of block c (6 in the case of July), then evaluates the condition, and takes the total of block b. This obviously shows the wrong result, displaying the year total of the reference slice instead of the year total of the algorithm.

Note the mismatch of the total displayed after block d (correct value) and the value display in block g (rt(d), which is the same as the FY REF value).

Can I force board to apply the algorithm on the month-level, but only display the slice sum? In case I'm taking the wrong approach, please advise what stategy I should use instead.

Daniele Santandrea

Hello Sebastian Kappen,

you cannot force to apply the algorithm on the month-level.
In your case, you need to change the approach. I recommend you to create an ad-HOC cube that you calculate thorugh a dataflow calculation.

Regards,
Daniele