1 Reply Latest reply on Dec 17, 2016 10:34 PM by Shinichiro Murakami

    Calculate profit for years in which certain data is available

    Ian Barton

      hi all,

       

      I have hierachical sales (10 years history) and costs data (2/3/4 years history) at the following levels. Im trying to create a calculated field "PROFIT" for only the years that i have sales and costs data bearing in mind some products have 2 or 3 or 4 years historical costs data. How would i write a formula to

      1) Create profitability by year which reflects the amount of data is available. Ie if there is 2 years costs data,id like to zee 2 years profitability. If there is 4 years costs, Id like to see 4 years profitability and see the other years blanked out

      2) Be able to measure profitability at the each of the hierachical levels below

       

       

       

      SALES: 10 years of Sales data with following levels

      1) Brand

      2) Franchise (which contains multiple brands)'

      3) Business Unit which contains multiple franchises)

      4) Country (Which contains multiple Business Units)

       

      COSTS: 2 years of Costs data which is only displayed at

      1) Brand level