Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a table similar to the following which shows the supply of products by 3 suppliers and the amount they supplied each week.
Week | Supplier | Amount |
1 | A | 451 |
1 | B | 101 |
1 | C | 594 |
2 | A | 459 |
2 | B | 921 |
2 | C | 66 |
3 | A | 255 |
3 | B | 772 |
3 | C | 579 |
4 | A | 441 |
4 | B | 32 |
4 | C | 0 |
5 | A | 336 |
5 | B | 171 |
5 | C | 798 |
I want to convert that into proportions so that I can use them for forecasting purposes later. Basically the proportion of each weeks supply by each supplier (each week will sum to 1). So I need to create a field like this
Week | Supplier | Proportion |
1 | A | 0.393543 |
1 | B | 0.088133 |
1 | C | 0.518325 |
2 | A | 0.317427 |
2 | B | 0.636929 |
2 | C | 0.045643 |
3 | A | 0.15878 |
3 | B | 0.480697 |
3 | C | 0.360523 |
4 | A | 0.932347 |
4 | B | 0.067653 |
4 | C | 0 |
5 | A | 0.257471 |
5 | B | 0.131034 |
5 | C | 0.611494 |
How can I achieve this in the script?
Thanks
A
You can use the following script:
Table:
LOAD Week,
Supplier,
Amount
FROM
[https://community.qlik.com/thread/164567]
(html, codepage is 1252, embedded labels, table is @1);
Join(Table)
LOAD Week,
Sum(Amount) as TotalAmount
Resident Table
Group By Week;
Table1:
LOAD Week,
Supplier,
Amount,
TotalAmount,
Amount/TotalAmount as Proportion
Resident Table;
DROP Table Table;
Output:
You can use the following script:
Table:
LOAD Week,
Supplier,
Amount
FROM
[https://community.qlik.com/thread/164567]
(html, codepage is 1252, embedded labels, table is @1);
Join(Table)
LOAD Week,
Sum(Amount) as TotalAmount
Resident Table
Group By Week;
Table1:
LOAD Week,
Supplier,
Amount,
TotalAmount,
Amount/TotalAmount as Proportion
Resident Table;
DROP Table Table;
Output: