Aggregation Formula not working
Hello,
I am having a hard time trying to make a formula work in a summary output module:
The dimensions are: time, r&d cost voice (list items: reticles, moves, licenses, services, labor, depreciation, general expenses, other) and cost center.
What I expect the formula to do is to get the values from the calculation modules when the list item corresponds to "moves", "depreciation", "labor". When it comes to "other", it should aggregate the values coming from the calculation modules of "reticles", "services", "licenses" and "general expenses".
The formula returns some zeros for reticles, licenses, services and general expenses, so that the summary of "All R&D Cost voices" sums: reticles + licenses + services + general expenses + other.
Apparently, it works but it returns a mistake. In fact, it does not consider the value of the reticles at all: 8,442.17 is the value of the general expenses, while the reticles amount to 126,526.45. Thus, the value of other should be the sum of the two.
The calculation modules for reticles and general expenses are structured as follows:
Thanks in advance,
Luca
Answers
-
Hi @LucaRicci,
It seems that the module R&D13 has different dimensionality than the output module, maybe the problem is there?
I would start investigating the issue by creating a test line item where you try only the part of the formula which takes the reticles values and go from there.
Br,
Niko
0 -
@nvilkko Hello, I tried from this module as well:
it has the same dimensionality but nonetheless it fails to return the correct value.
As you suggest, I even tried to test the formula by only referencing the reticles and nothing else, it works. The issue occurs after the sentence: 'IF R&D Cost Voice = Reticles' THEN 0. What I don't understand is the reason why it works for the general expenses and it does not for the reticles, given that the conditions are the same.
0 -
Not sure if this fixes the issue, but I don't think you need the condition 'IF R&D Cost Voice = Reticles' THEN 0' or any other where the result is 0. You could just write the if conditions where you want the values and then end it with 'ELSE 0'
0 -
If you drill down to the cell, can you see the Reticles value?
You can drill down by pressing F8 or by right clicking the cell and selecting 'Drill down'.
0 -
I think the problem is that you have the same list 'R&D Cost Voice' in both source and target modules. So, even though you have the condition 'R&D Cost Voice = Other', it still checks the 'R&D Cost Voice = Reticles' in R&D15 for example. Is it possible that in R&D37 you have the same value (8,442) if you look at Other?
However, I think one of these solutions could resolve the issue:
- If you have only data for the specific R&D cost voice in your source modules and you have a top level item for the list, you could write the formula in this way:
'R&D15 Total Check - reticles'. Total SAP Amount ITA GAAP[SELECT: 'R&D Cost Voice'. Top Level Item] - If the first solution doesn't work for you, then you need to lookup the correct 'R&D Cost Voice' item. E.g.
'R&D15 Total Check - reticles'. Total SAP Amount ITA GAAP[LOOKUP: Reticles].
However, sometimes there are issues with looking up a list item when you have that same list in your target module, so you may need to use select. E.g.
'R&D15 Total Check - reticles'. Total SAP Amount ITA GAAP[SELECT: 'R&D Cost Voice'.Reticles]. But using select like this is not best practice and doesn't work if 'R&D Cost Voice' is a production list.0 - If you have only data for the specific R&D cost voice in your source modules and you have a top level item for the list, you could write the formula in this way:
