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
Answers
-
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: Formula0 -
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
0 -
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.
0 -
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
0 -
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.
For more detail, see the blueprint attached.
I hope it helps,
Taichi
0 -
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
0 -
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.0
