Workaround for select in LIS Collected.

Dear Team,

I would like to ask for a workaround in case where I had to Compare the different versions(Custom) side by side to show the variance and growth without using select. What I did are the following:
1. In my report Module, I made the GL as line item (Revenue, COS, Gross Profit) for some reason. I have also calculated the GP Margin and COS Margin in the same module per Version.
2. In the Variance module, I collected the whole results and created the line items (Actual, budget, PY) along with its variance and growth line items using lookup(Version)

My concern though, is that i cannot bring the amount and the ratio together in the ratio due to summary that cannot use Formula, so the lowest level GP is correct but the higher hierarchy of the reporting Department/entity will no longer be correct.

I used Select here in the Variance line item and it worked but I would like to ask for a workaround in order for me to be able to create this chart in the ux:

Appreciate all your help!

Elaine

Accepted answers

All comments

AntonMineev

Hi @elaine.novel

What exactly are the wrong readings on the summing elements?

If they are percentages, you can recalculate them by putting the numerator and denominator together separately.

Example:

% Margin
Margin Numerator: Margin[LOOKUP: XXX], SUM
Margin Denominator: Revenue[LOOKUP: XXX], SUM
% Margin = Numerator/Denominator. Summary format: Formula

elaine.novel

Hi Anton,

The Gross Profit and the Gross Margin has to be LIS..and I need them to roll up to the hierarchy as formula using Lookup, rather than select as Summary. The line item for this is the Actual, Budget, Prior Year 1, Prior Year 2 and Prior Year 3 and it has to roll up as formula.

Thanks,

Elaine

Alexandru.Rosca

Hi Elaine, could you share the setup of your modules? Are you using a custom version list that includes Actuals, Budget, PY and also their comparisons (Actuals vs Budget), or you defined through line items in Variance module?

I would go with the following approach:

1. Create line items subsets for your P&L lines from Reporting Module

2. In your Version list create 2 properties: Compared & Compared with (list formatted) where you will define the version for lookup comparison. Or ideally do this in a system module.

3. in Variance Module create 2 line items: 1 for collecting the data (say we name it "Collect data" and the second one for performing variance calculation), will look like this: Collect data[lookup: version list.compared] - collect data [lookup: version list.compared with].

The only downside is that you cannot individually format the units to display numbers and percentages in the same time.

elaine.novel

Hi Alexandru,

for your first question on whether this is Custom Version List, Yes Correct.

on your solution. This is what I have done as well. But the problem is, if my client will need to show both the Gross Profit (Absolute Amount) and the Gross Profit Margin at the same time. The lowest level will be correct, but the higher hierarchy of the GP Margin will no longer be correct since the lookup cannot be summarized using FORMULA.

I have gone through the Planual 2.02.14a and it indicates exception in using select for Versions. I would like to confirm with the Community that I can use Select so i can get the monthly trend across all level for both the GP and the GP Margin.

Thanks,

Elaine

Amaya

Hi @elaine.novel ,

This approach may not be elegant.

We can use the if statement and change the behavior of each LIS.

Here is the System module for the calculation above.

LIS setting.png

For more detail, see the blueprint attached.

I hope it helps,

Taichi

PL_Variance_139241.xlsx

elaine.novel

Hi Taichi,

Appreciate your efforts but I have actually done this already. The thing with LIS is it is also rolling up to parents based on the summation created in the module.. so the lookup to get the parent won't work. the GL Codes (Revenue, Gross Profit to the Gross Margin) are all LIS.

This is the sample chart output that needs to be produced out of the module.

1.Actual - Abs and %

2. Budget - Abs & %

3. PY1 - Abs & %

4. PY2 - Abs & %

4. PY-3 - Abs & %

Appreciate your assistance on this.

Elaine

Amaya

Sorry for my lack of Explanation.
The "A" above is Parent of A1,A2,A3,A4.
The Summary of GP Margin is 'Formula', so the GP Margin is the correct figure of 0.4.

Quick Links

Can you help?

Unanswered questions

Unable to use one page for multiple DEV models that are partially in sync
Hello, Our model builders are normally making changes to all structural changes in a DEV model. Because we are now in a Prod freeze and we don't want to move structural changes to PROD until end of year, we created a copy of the DEV model (standard mode) - 'DEV2' model, where we are only making changes to urgent issues…
Anaplan for Microsoft 365
Using the excel addin for Microsoft 365 when I try to add a card to import data I get the message 'Cannot insert data from a custom view'. I am not using a custom view. Any ideas what I am missing here?
Data Split between Many to Many Relationship Mappings
Hello Anaplan Community, I am encountering an issue with one of my development work and would be greatly appreciated your assistance. There are 2 different modules involved in my model: 1) INP01 Percentage Input by Product (Applies To: P3 List) 2) REP01 Percentage Split by Department (Applies To: Departments List) The…