Monthly vs Quarterly allocation of accounts
Hello,
We are trying to automate a simple allocation of specific corporate expense accounts. The accounts allocate to different business based on their contribution to total GP. I currently have two calculations:
- Account Dynamic Calc - BU GP / Total GP, giving the business GP%
- UD8 Dynamic Calc - GP% * Corporate Expense Account, giving the expense to be allocated to each BU
I run into an issue when data is pulled as either QTD or YTD. As an example, if someone is to pull the data at 2025M3/QTD, it will calculate calculate a separate GP% for the QTD information and complete the calculation. This creates a discrepancy when looking at data monthly vs. quarterly. Instead, I would need it to add together months 1-3 for consistency. (See below table for reference)
Is there a way to make a dynamic calculation calculate only at the monthly level and instead aggregate months for other view selections (QTD/YTD)?
| What is happening | What I need | ||||
| Calculation | QTD GP * QTD GP% | M1 + M2 + M3 | |||
| MTD | M1 | M2 | M3 | Q1 / M3 QTD | Q1 / M3 QTD |
| Business GP | 100,000 | 400,000 | 200,000 | 700,000 | 700,000 |
| Total GP | 500,000 | 1,000,000 | 900,000 | 2,400,000 | 4,300,000 |
| GP % | 20.0% | 40.0% | 22.2% | 29.2% | |
| Corporate Expense | 10,000 | 50,000 | 20,000 | 80,000 | |
| Allocation | 2,000 | 20,000 | 4,444 |
23,333 (29.2% * 80,000) | 26,444 (M1 + M2 + M3) |
Your table confuses me. But when I see division and parent members involved, there are almost always order of operation issues to consider that can only be solved through Dynamic Calcs.
Depending on what you need to do, you can choose to vary your formula based on the View such as:
If api.Pov.Time.MemberId=DimConstants.Periodic Then
'Periodic formula
ElseIf api.Pov.Time.MemberId=DimConstants.QTD Then
'QTD formula
End If
Or, you can include V#Periodic in each operand of your formula, so that regardless of the user's POV View, you will compute using Periodic.
Divide(A#BUGP:V#Periodic,A#TotalGP:V#Periodic)
