1 Reply Latest reply: Jun 5, 2014 9:38 PM by Eduardo Sommer RSS

    Calculate Contribution % for last 3 Years only

      productfyearcurrentyrsale
      Record1mp320092014$1,010
      Record2tv20092014$30,000
      Record3tv20102014$250
      Record4camera20102014$40,000
      Record5camera20112014$39,620
      Record6mp320122014$48,342
      Record7tv20112014$57,064
      Record8tv20132014$65,786
      Record9camera20132014$74,508
      Record10camera20142014$83,230
      Record11mp320142014$91,952
      Record12tv20142014$57,064
      Record13tv20142014$65,786
      Record14camera20092014$74,508
      Record15camera20102014$83,230
      Record16mp320112014$91,952
      Record17tv20112014$100,674
      Record18tv20112014$109,396

       

      I need a result table something like this...

       

      productContributionTotal201220132014
      Total100%150354570
      mp330%45101520
      tv43%65202025
      camera27%4051025

       

      For individual years & totals, I am using these 4 expressions (with product as a dimension):

      2014: Sum (if (fyear = currentyr, net_price_amt, 0))

      2013: Sum (if (fyear = currentyr-1, net_price_amt, 0))

      2012: Sum (if (fyear = currentyr-2, net_price_amt, 0))

      All 3: Sum (if (fyear = currentyr, net_price_amt, 0))+Sum (if (fyear = currentyr-1, net_price_amt, 0))+Sum (if (fyear = currentyr-2, net_price_amt, 0))

       

      For Totals (last 3 years only), I am using this expression below, but it is not working .

      Aggr(Sum (if (fyear = currentyr, net_price_amt, 0))+Sum (if (fyear = currentyr-1, net_price_amt, 0))+Sum (if (fyear = currentyr-2, net_price_amt, 0)), pldesc) / Sum(TOTAL if (fyear = currentyr, net_price_amt, 0))+Sum (TOTAL if (fyear = currentyr-1, net_price_amt, 0))+Sum (TOTAL if (fyear = currentyr-2, net_price_amt, 0))

       

      Please note, I have to eliminate 2011 data, hence the expression above.

      What am I doing wrong?

      Thanks in advance !

       

      Please note, If I didn't have other fiscal years in my underlying data, I would just use this as the expression and it works fine:

      Aggr(sum(net_price_amt),pldesc) / sum(TOTAL net_price_amt)