How to calculate the total amount without using Select function

Options
alexlee
alexlee Member, ALL USERS, Partner, Certified Model Builder Posts: 5 New Contributor
in Planning & Modeling

Hi All,

 

I have created a module with two list (Company list: A,B,C) and Fruit list (Apple, Orange, Pineapple)

Cap1.jpg

 

Let say company A and C are tier 1 and company B is tier 2

 

And then i have created another module without any list dimension but just only a line item, how can i calculate the total amount of Apple selling by tier 1 company?

Cap2.jpg

 

Thanks!!

 

Tagged:

Comments

  • anikdas
    anikdas Member, ALL USERS, GroupMember, Master Anaplanner - Employee, Certified Master Anaplanner, Employee, Certified Model Builder Posts: 5 Master Anaplanner of the Year

    Hi @alexlee 

    You can use a global lookup to do this instead of using select. However, you can't go straight from Company to tiers, you will need a module by tiers and fruits. Then you can use a lookup module to write the formulas.

    Input Data:

    anikdas_0-1652237165292.png

    Sales by Tier and Fruit:

    anikdas_1-1652237197755.png

    Global Lookup Module:

    anikdas_2-1652237217980.png

    Final Summary:

    anikdas_3-1652237244316.png

     

    Glad that you do not want to use SELECT, there are many drawbacks of using select including performance and hindrance to ALM. However, if you need the amount across all time periods, you will need to use Select on Time. All Periods.

    Hope this helps.

Categories