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: 
Not applicable

Weighting Logic in Qlikview Model

Hi,

I'm hoping someone can help me with a problem that i'm facing. I'm trying to implement allocation logic for a field in Qlikview based on another field. The data that i have includes customer, product and quantity as listed in the table below. I'm trying to compute the net quantity by product (sum of all quantities for a specific product across all clients) which i've been able to calculate using a load script in the editor and allocate it across the customers with the largest quantities. Below is an example of the output i'm trying to create. In this example, we have multiple customers that are buying and selling a product XXX. The net quantity for product XXX is 8,000 that I need to allocate to the clients with the biggest amounts. In this case, the net quantity should get allocated to clients JKL and MNO (client JKL is buying 5,000 and MNO is buying 3,000 which totals the net quantity of 8,000). The allocated quantity cannot exceed the Quantity against each client.

CustomerProductDirectionQuantityNet QuantityAllocated Quantity

ABC

XXXBuy1,0008,0000
DEFXXXBuy2,0008,0000
GHIXXXSell-3,0008,0000
JKLXXXBuy5,0008,0005,000
MNOXXXBuy3,0008,0003,000

Any thoughts on how i can implement this either in the editor through a load script or as an expression in a table or a chart?

Thanks,

AC

5 Replies
sinanozdemir
Specialist III
Specialist III

Hi,

I am not sure if this is going to work, but you can rank your customers based on the quantity:

Capture.PNG

Then you can choose the top 2 customers with their quantities:

Capture.PNG

Hope this helps.

Not applicable
Author

Thank you for getting back to me. The problem is, sometimes there are more than 2 customers that make up the net quantity. So the model needs to be smart enough to recognize that.

aarkay29
Specialist
Specialist

PFA

I might have missed some scenarios where it may show incorrect numbers. But considering those the script have to be adjusted.

Thanks,

Aar

sinanozdemir
Specialist III
Specialist III

Hi,

If there are more than two customers that make up the net quantity, then please provide those customers in your sample data.

Thanks

Not applicable
Author

Hi Sinan,

The below is a real example from my dataset. I've been able to compute the Net Quantity in the code. The portion i'm struggling with is the Rank and Allocated Quantity. The Rank should display the top few values in the quantity column depending on the sign i.e. if the Net Quantity is negative, it should rank only the negative values in the Quantity field by product/trade id and if the Net Quantity is positive, it should rank all the positive quantities only. The Allocated Quantity should equal the Quantity depending on the direction (if the Net Quantity is negative, allocated quantity will only pick up the negative quantities, etc.) until the sum of the allocated quantity exceeds the Net Quantity. In the below example, after rank 9, the total allocated quantity exceeded the net quantity. So for the customer and trade id in Rank 9, the allocated quantity is just the difference between the sum of the allocated quantity and net. This way, the total allocated quantity equals the net quantity. I hope this isn't too confusing. Any thoughts on how i can implement this will be helpful.

Thanks

DateCounterparty NameProductTrade IdQuantityNet QuantityRankAllocated Quantity
2017-07-17ABCXXXN318829E-1279138-74111712-1279138
2017-07-17DEFXXXN318828E-1131124-74111713-1131124
2017-07-17GHIXXXN318827E-63919-7411171120
2017-07-17JKLXXXN317444EE-784000-74111715-784000
2017-07-17JKLXXXN317672EE-840000-74111714-840000
2017-07-17JKLXXXN317864EE-446419-74111716-446419
2017-07-17JKLXXXN319615EE-155000-74111719-118230
2017-07-17JKLXXXN319640EE-435000-74111717-435000
2017-07-17JKLXXXN320890EE-145000-7411171100
2017-07-17MNOXXXN318833E-2195304-74111711-2195304
2017-07-17PQRXXXN318832E-181956-74111718-181956
2017-07-17STUXXXN312187E17094-741117100
2017-07-17STUXXXN312319E16936-741117100
2017-07-17STUXXXN313100E236000-741117100
2017-07-17STUXXXN313771E29128-741117100
2017-07-17STUXXXN313772E29128-741117100
2017-07-17STUXXXN314252E20105-741117100
2017-07-17STUXXXN320652E51857-741117100
2017-07-17VWXXXXN318831E-11580-7411171140
2017-07-17XYZXXXN318834E-116908-7411171110
2017-07-17ABCDXXXN318830E-20071-7411171130
2017-07-17EFGHXXXN317902E-6000-741117115