Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a problem with an analisys that i am trying to do and i hope you can help me.
I have a list of items produce for date, type of package and supplier. To get the total weight produced per week I use the following script :
TABLE_1:
LOAD date,
Week(date) as Week,
supplier,
Item,
package,
amount_boxes,
weight_box
FROM
(qvd);
TOTAL_PRODUCE:
LOAD week,
supplier,
Item,
SUM(amount_boxes*weight_box) AS total_weight
Resident TABLE_1
Group by week,supplier,Item;
Then I want to show the development of the types of package produced in percentage for each week. For that I used as dimensions week and package, and as expression %= SUM(amount_boxes*weight_box)/SUM(total_weight)
But I have the following problem, not for each week all the suppliers are produce each type of package, for example for certain item selected in week 19 several suppliers haven’t produce the package 235, so the total weigh for this type package is below the total of the week (858416,4).
Week | Package | % | SUM(amount_boxes*weight_box) | SUM(total_weight) |
100,00% | 14637138,3 | 14637757,5 | ||
18 | 200+ | 100,00% | 9624 | 9624 |
19 | 75 | 13,96% | 119488,4 | 856090,4 |
19 | 95 | 10,91% | 93430,4 | 856090,4 |
19 | 115 | 32,20% | 275664,4 | 856090,4 |
19 | 140 | 21,45% | 183610 | 856090,4 |
19 | 165 | 15,95% | 136525,8 | 856090,4 |
19 | 200+ | 5,78% | 49594,2 | 858416,4 |
19 | 235 | 0,03% | 103,2 | 353771,6 |
20 | 75 | 15,69% | 131184,4 | 836334,6 |
20 | 95 | 11,47% | 95890 | 836334,6 |
20 | 115 | 32,22% | 269438 | 836334,6 |
20 | 140 | 21,20% | 177280,4 | 836334,6 |
20 | 165 | 14,62% | 122261,2 | 836334,6 |
20 | 200+ | 4,24% | 35430,2 | 836334,6 |
20 | 235 | 0,75% | 4850,4 | 649440,8 |
I can’t remove the supplier of the script because sometimes I need to use it as a filter and calculate the development of package for this particular supplier, but I don´t know how to obtain the total weight of the week that changes due to the selection of item and supplier.
I would appreciate any help.
Thanks
But I have the following problem, not for each week all the suppliers are produce each type of package, for example for certain item selected in week 19 several suppliers haven’t produce the package 235, so the total weigh for this type package is below the total of the week (858416,4).
The week total for package 235 is not 858416,4. That's the week total for package 200+. And both are different from the week total of package 165. Why do you think it should be 858416,4?
Hi Gysbert, I want to show the percentage distribution of what is produced weekly and how it has been changing every week. I mean, I want to divide the produced amount for each package by the total produced in that week. In this case, the week 19:
Week | Package | % | SUM(amount_boxes*weight_box) | SUM(total_weight) |
---|---|---|---|---|
TOTAL | 100,27% | 858.416,40 | 858.416,40 | |
19 | 75 | 13,96% | 119.488,40 | 856.090,40 |
19 | 95 | 10,91% | 93.430,40 | 856.090,40 |
19 | 115 | 32,20% | 275.664,40 | 856.090,40 |
19 | 140 | 21,45% | 183.610,00 | 856.090,40 |
19 | 165 | 15,95% | 136.525,80 | 856.090,40 |
19 | 200+ | 5,78% | 49.594,20 | 858.416,40 |
19 | 235 | 0,03% | 103,20 | 353.771,60 |
The total amount produce in this week is is 858416,4.