Hi,
I want to calculate a division. The Data and logic goes as follow:
| VERSION | PRODUCT | AMT | MARGIN | QTY | COST (AMT-MARGIN/QTY) | NEED | HOW ? |
| ** | A | 1800 | 1200 | 1200 | $ 0.50 | 1.00 | COST( **,A ) / COST(**,A) |
| ** | B | 400 | 150 | 400 | $ 0.63 | 1.00 | COST( **,B) / COST(**,B) |
| ** | C | 1000 | 600 | 500 | $ 0.80 | 1.00 | COST( **,C) / COST(**,C) |
| *AI | A | 300 | 169 | 40 | $ 3.28 | 6.55 | COST( *AI,A ) / COST(**,A) |
| *AI | B | 320 | 170 | 50 | $ 3.00 | 4.80 | COST( *AI,B) / COST(**,B) |
| *AI | C | 400 | 110 | 70 | $ 4.14 | 5.18 | COST( *AI,C) / COST(**,C) |
| *AI | D | 300 | 170 | 30 | $ 4.33 | 0.00 | COST( *AI,D) / COST(**,D) [**, D not exists] |
| *BP | A | 350 | 180 | 60 | $ 2.83 | 5.67 | COST( *BP,A ) / COST(**,A) |
| *BP | B | 380 | 192 | 120 | $ 1.57 | 2.51 | COST( *BP,B) / COST(**,B) |
| *BP | C | 420 | 230 | 45 | $ 4.22 | 5.28 | COST( *BP,C) / COST(**,C) |
| *CA | A | 390 | 200 | 40 | $ 4.75 | 9.50 | COST( *CA,A / COST(**,A) |
| *CA | B | 390 | 210 | 70 | $ 2.57 | 4.11 | COST( *CA,B) / COST(**,B) |
| *CA | D | 360 | 190 | 90 | $ 1.89 | 0.00 | COST( *CA,D) / COST(**,D) |
The logic is : All costs should be divided by the Cost of ** with their respective Product.
for example:
Row 1: cost (**, Product A) was divided by same thing. - so it's 1.
Row 4 : (Blue marked)
Cost of (*AI, Product A) was divided by Cost (**, Product A)
also, if product not exists in **, it can write 'Zero'. exmple marked in RED.
Here is the working query with out having Product column. Now looking for with Product column.
((Sum(TOTAL {<VG = {"POS"}, VERSION = {'**'}, METRIC = {"AMT $"}>} VALUE)/1000000) -
(Sum(TOTAL {<VG = {"POS"}, VERSION = {'**'}, METRIC = {"MARGIN $"}>} VALUE)/1000000))/(Sum(TOTAL {<VG = {"POS"}, VERSION = {'**'}, METRIC = {"QTY #"}>} VALUE)/10000)
Thanks again to Sunny_talwar for that.
Appreciate your Help, Thanks.
