Discussion Board for collaboration related to QlikView App Development.
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.
|Customer||Product||Direction||Quantity||Net Quantity||Allocated Quantity|
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?
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.
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.
|Date||Counterparty Name||Product||Trade Id||Quantity||Net Quantity||Rank||Allocated Quantity|