Hello All.. request you to help me out on the below:
Base data
Expected Pivot for the above data:
What's different in this report:
1) I have manually calculated and removed the earnings. I have calculated only POSITIVE values and ignored all Negative values
2) I have combined calculations of Product No 1 - 3 and combined calculations for the remaining products
Can the above 2 calculations be made possible using Pivot. I know its very much possbile using SUMPRODUCT. However, I prefer Pivot.
Please help.
Thanks
Base data
Date | Details | Product No. | Amount |
1-Jan-20 | Purchase XX grams | 1 | 8456 |
1-Feb-20 | Purchase XX grams | 2 | 6684 |
22-Jan-20 | Purchase XX grams | 6 | 3264 |
23-Jan-20 | Purchase XX grams | 12 | 2630 |
24-Jan-20 | Purchase XX grams | 9 | 9951 |
15-Mar-20 | Purchase XX grams | 11 | 6687 |
22-Apr-20 | Purchase XX grams | 1 | 5623 |
1-May-20 | Purchase XX grams | 3 | 5548 |
4-May-20 | Sold product | 1 | -25000 |
27-Jun-20 | Purchase XX grams | 4 | 4565 |
11-Aug-20 | Purchase XX grams | 2 | 5585 |
26-Aug-20 | Purchase XX grams | 1 | 4468 |
29-Aug-20 | Purchase XX grams | 3 | 5960 |
30-Aug-20 | Purchase XX grams | 7 | 5443 |
2-Sep-20 | Purchase XX grams | 2 | 8765 |
4-Sep-20 | Purchase XX grams | 11 | 4568 |
9-Sep-20 | Purchase XX grams | 1 | 4495 |
11-Sep-20 | Purchase XX grams | 5 | 7843 |
13-Sep-20 | Sold product | 3 | -50000 |
15-Sep-20 | Purchase XX grams | 3 | 5689 |
Expected Pivot for the above data:
Sum of Amount | Product No. | |
Months | 1, 2 & 3 | Other Products |
Jan | 8456 | 15845 |
Feb | 6684 | |
Mar | 6687 | |
Apr | 5623 | |
May | 5548 | |
Jun | 4565 | |
Aug | 16013 | 5443 |
Sep | 18949 | 12411 |
Grand Total | 61273 | 44951 |
What's different in this report:
1) I have manually calculated and removed the earnings. I have calculated only POSITIVE values and ignored all Negative values
2) I have combined calculations of Product No 1 - 3 and combined calculations for the remaining products
Can the above 2 calculations be made possible using Pivot. I know its very much possbile using SUMPRODUCT. However, I prefer Pivot.
Please help.
Thanks