Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Creating proportion field in script

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.

WeekSupplierAmount
1A451
1B101
1C594
2A459
2B921
2C66
3A255
3B772
3C579
4A441
4B32
4C0
5A336
5B171
5C798

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

WeekSupplierProportion
1A0.393543
1B0.088133
1C0.518325
2A0.317427
2B0.636929
2C0.045643
3A0.15878
3B0.480697
3C0.360523
4A0.932347
4B0.067653
4C0
5A0.257471
5B0.131034
5C0.611494

How can I achieve this in the script?

Thanks

A

1 Solution

Accepted Solutions
sunny_talwar

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:

Capture.PNG

View solution in original post

1 Reply
sunny_talwar

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:

Capture.PNG