Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
If I understood your requirementes, the attached qvw will help you.
Eduardo