Hi,
I have been asked to create a pivot table that will show by month and year the following calculation:
[UnitAmount] * [PriceForUnit]
The final table that I would like to display is:
| | | | Year | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 | 2019 |
| Product | UnitAmount | PriceForUnit | Month | April | August | December | February | July | June | March | May | November | October | September |
| A | 6 | 220.1 | | 1,320.6 | 1,320.6 | 1,320.6 | 1,320.6 | 1,320.6 | 0 | 1,320.6 | 1,320.6 | 1,320.6 | 1,320.6 | 0 |
| A | 12 | 220.1 | | 2,641.2 | 2,641.2 | 0 | 2,641.2 | 0 | 2,641.2 | 2,641.2 | 2,641.2 | 0 | 0 | 0 |
| A | 18 | 0 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| A | 18 | 220.1 | | 0 | 0 | 0 | 3,961.8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
I create a dummy data:
LOAD * INLINE [
Product, UnitAmount, PriceForUnit, Month, Year
A, -18, 0, June , 2019
A, 6, 220.1, February, 2019
A, 6, 220.1, March , 2019
A, 6, 220.1, April , 2019
A, 6, 220.1, May, 2019
A, 6, 220.1, July , 2019
A, 6, 220.1, August, 2019
A, 6, 220.1, October, 2019
A, 6, 220.1, November , 2019
A, 6, 220.1, December, 2019
A, 12, 220.1, February, 2019
A, 12, 220.1, March , 2019
A, 12, 220.1, April , 2019
A, 12, 220.1, May, 2019
A, 12, 220.1, June , 2019
A, 12, 220.1, August, 2019
A, 18, 0, June , 2019
A, 18, 0, September, 2019
A, 18, 220.1, February, 2019
];
