Trying to SUM revenue by account and product, getting datatype mapping error

Teddy5
Teddy5
in Modeling

I am trying to sum the revenue data in the "PY Account and Product Revenue" line item by account. Source and target module are both dimensioned by the same account>product combo list. I keep getting the same "Datatype mapping used for aggregation doesn't match any dimension of the result" error. Line item types are all correctly set to the appropriate lists. Don't understand why the SUM formula is not working. Thank you!

image.png

Best Answer

  • shubhamrawat
    shubhamrawat
    Answer ✓

    Hi @Teddy5

    Here, Sum will not work, as your source "PY Account and Product Revenue" and target "PY Account Revenue" line item are both dimensioned by A2 Account>Product#, and so is your mapping table "SYS03 Account>Product Details" module, simply use LOOKUP instead of SUM, it will give aggregated value for the Account.

    Hope this helps.

Answers

  • Teddy5
    Teddy5

    Thanks, that worked! I guess I am missing the intuition behind why LOOKUP is a better choice than SUM here.

  • Teddy5
    Teddy5

    @shubhamrawat additionally, LOOKUP is not working when I try to aggregate by Product Family or by Country, presumably because the modules are not dimensioned by these. Though these line items are already loaded into my module, I am still struggling to aggregate revenue by these dimensions. Any suggestions? Thank you!

    image.png

  • shubhamrawat
    shubhamrawat
    edited June 2

    Hi, since this a training model, I can give you a hint, you might need an additional module with these dimensions from where you can lookup the revenue.

    Also, you might find this useful for understanding SUM vs Lookup functions.

    https://community.anaplan.com/discussion/533/when-do-i-use-sum-vs-lookup

  • Teddy5
    Teddy5

    Appreciate it, thanks!

Categories