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

Accepted answers

All comments

nvilkko

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

LucaRicci

@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.

nvilkko

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'

LucaRicci

@nvilkko Nope, still not working :(

nvilkko

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'.

LucaRicci

@nvilkko yes, of course. As you can see it's only taking into account the general expenses:

nvilkko

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.

LucaRicci

@nvilkko it seems to be working with the first select solution! many thanks :)

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…