Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have these there table:
item number | per unit |
100 | 90 |
200 | 40 |
300 | 30 |
500 | 20 |
400 | 50 |
item number | Date | qty |
100 | 5/20/2016 | 1000 |
200 | 6/20/2016 | 2000 |
300 | 7/20/2016 | 3000 |
400 | 8/20/2016 | 4000 |
100 | 5/20/2017 | 2000 |
200 | 6/20/2017 | 3000 |
300 | 7/20/2017 | 4000 |
400 | 8/20/2017 | 5000 |
Date | year | month |
5/20/2016 | 2016 | 5 |
6/20/2016 | 2016 | 6 |
7/20/2016 | 2016 | 7 |
8/20/2016 | 2016 | 8 |
5/20/2017 | 2017 | 5 |
6/20/2017 | 2017 | 6 |
7/20/2017 | 2017 | 7 |
8/20/2017 | 2017 | 8 |
How would I calculate the total Qty by per unit based on year?
the result should be like these:
Row Labels | qty/per unit |
2016 | 241 |
2017 | 331 |
Grand Total | 572 |
and the detail table is:
item number | Date | qty | unit | Qty/per unit | year | month |
100 | 5/20/2016 | 1000 | 90 | 11.11111111 | 2016 | 5 |
200 | 6/20/2016 | 2000 | 40 | 50 | 2016 | 6 |
300 | 7/20/2016 | 3000 | 30 | 100 | 2016 | 7 |
400 | 8/20/2016 | 4000 | 50 | 80 | 2016 | 8 |
100 | 5/20/2017 | 2000 | 90 | 22.22222222 | 2017 | 5 |
200 | 6/20/2017 | 3000 | 40 | 75 | 2017 | 6 |
300 | 7/20/2017 | 4000 | 30 | 133.3333333 | 2017 | 7 |
400 | 8/20/2017 | 5000 | 50 | 100 | 2017 | 8 |
Try this expression
Sum(qty/[per unit])
Thank you