Formula SUM

ChrisG532
ChrisG532
edited January 5 in Modeling

I have

A List of Farms - 'FarmNames' - showing the Farm name, and a Property - 'Shop NameShop Name' -which shows the shop the farm serves.

A module - 'Farm Apples' - showing the name of the Farm, and the manually entered number of apples by month for the past two years, where apples is the only line item.

I want to show -

how many apples each shop has bought

which Farm Serves each store.

I think SUM is the right function to use, but I can'can't make the formula work.

I have tried -

Farm Apples.Apples [SUM:Farm Names.Shop Name]

'Farm Apples' is dimensioned using 'Farm Names' list, which has the Property 'Shop Name'. (and Time)

I was expecting to see a neat summary of the apples sold to a shop, but my formula doesn't work. I've re-run the Level 11module but I still can't see where I'm going wrong.

I know this is not the mosmost complex question, but I'm stumped!

What am I doing wrong here?

Chris.

Best Answer

  • ChrisG532
    ChrisG532
    Answer ✓

    I know its taken a while to reply, but thanks for your help here.

    Prompted by you, I decided to go back to the 'text book' and refresh my understanding. I can see how your response fits.

    I've now fixed my problem and pick up some learning of value. Thanks.

Answers

  • mhatlasi
    mhatlasi
    edited January 5

    Well, here are a few points that come to my mind.

    Your 'shop' property needs to also be a list item, otherwise you can't analyze by it.

    Also, its best to not store it as a list property, but in a SYS module that maps your Farm list to your Shop list with dimension : Farm and line item: Shop (data type: list → shop) and no time dimension.

    Let's call this 'MAP01 Farm to Shop'

    Let's assume your module that stores count of apples is called 'DAT01 Apple Count'.

    Then, your analysis module will need to have the 'Shop' list as a dimension,
    with one line item for 'Apple Count', data type number.

    Let's call this new module 'CALC01 Apple by Shop'

    Your formula for capturing data would look something like this:
    'DAT01 Apple Count'.'Quantity'[sum: 'MAP01 Farm to Shop'.'Shop']

    That should solve it for you.


    EDIT: I see you reworded your question,

    My first hypothesis is that your 'Shop/Store' is not a list value.

    Your 'destination' module should also have the 'Shop' list as a dimension to make it work.

Categories