Calculate Contribution % for last 3 Years only

Fri, 06 Jun 2014 00:41:22 GMT

	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?

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)

Re: Calculate Contribution % for last 3 Years only

eduardo_sommer — Fri, 06 Jun 2014 01:38:41 GMT

If I understood your requirementes, the attached qvw will help you.

Eduardo

topic Calculate Contribution % for last 3 Years only in QlikView

Calculate Contribution % for last 3 Years only

Re: Calculate Contribution % for last 3 Years only