Different Aggregations for Different Dimensions

Question

I have a REP01 module with Account 2 (R1, R2, L1, L2, E1, E2) and City(City 1, City 2, …) dimensions and an Amount line item.

City rolls up to Region (Region 1, Region 2)

Account 2 rolls up to Account 1 (Revenue, Labor, Expense).

In REP01 module's Amount line item, I want to sum in City dimension but  use a formula of Revenue - Labor - Expense in Account dimension.

I know I can accomplish this using an extra Amount line item and Ratio summary method with denominator= 1.

Can you help with dimensionality?

P.S. I want to use as few cells as possible since this is a multidimensional reporting module.

Anaplan Modeler · Answer

Hi @rob_marshall,

I built the solution you provided and it is working.

Thank you for your time. However, I am tripling the number of cells. Is there a way to accomplish this with fewer cells?

I see your Ratio line item is a subsidiary view. What's its dimension? I think you removed only the time dimension from it.

rob_marshall · Answer

@Sait

In a SYS Account, put the Signed Account in there (as you have it now, you also have it by City which is not needed.

in your current module, create a Temp and Ratio line item with the corresponding summaries.

in "my" New Way line item, change the summary to be a ratio.  I my new way, end users are inputting the data, but you can also make it a formula the way I did with Test so no one can input data.

Anaplan Modeler · Answer

@devrathahuja

I created Signed Amount and Abs Amount (Account 2) line items and got the expected output in All Accounts.

But there are two problems:

1. I had to double the cell count.

2. Those negatives at the Account 1 level should be positive.

@rob_marshall How do I build this at Account 1 level without negatives?