Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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.

Highlighted
Not applicable

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.

Highlighted
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

Highlighted
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

Highlighted
Not applicable

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