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

# Calculate Contribution % for last 3 Years only

 product fyear currentyr sale Record1 mp3 2009 2014 \$1,010 Record2 tv 2009 2014 \$30,000 Record3 tv 2010 2014 \$250 Record4 camera 2010 2014 \$40,000 Record5 camera 2011 2014 \$39,620 Record6 mp3 2012 2014 \$48,342 Record7 tv 2011 2014 \$57,064 Record8 tv 2013 2014 \$65,786 Record9 camera 2013 2014 \$74,508 Record10 camera 2014 2014 \$83,230 Record11 mp3 2014 2014 \$91,952 Record12 tv 2014 2014 \$57,064 Record13 tv 2014 2014 \$65,786 Record14 camera 2009 2014 \$74,508 Record15 camera 2010 2014 \$83,230 Record16 mp3 2011 2014 \$91,952 Record17 tv 2011 2014 \$100,674 Record18 tv 2011 2014 \$109,396

I need a result table something like this...

 product Contribution Total 2012 2013 2014 Total 100% 150 35 45 70 mp3 30% 45 10 15 20 tv 43% 65 20 20 25 camera 27% 40 5 10 25

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?

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)