Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone first time poster here I usually try and search up my own answers, but i've come across a problem that I haven't been able to solve yet and hoping for some help here.
Essentially i'm trying to calculate whether a product has sufficient stock in inventory in a load script, it needs to be part of the load script as the result of this calculation (sufficient stock, insufficient stock) will be used as dimensions for analysis.
As a watered down example, an input table (as a csv) is of the format:
Order Num | Product | Due Date | Order Qty | Total Inventory | Allocated Batch Num |
---|---|---|---|---|---|
10001 | APPLE | 5/5/2013 | 100 | 250 | |
10002 | APPLE | 6/5/2013 | 143 | 250 | B100 |
10003 | ORANGE | 6/5/2013 | 80 | 500 | |
10004 | GRAPE | 6/5/2013 | 35 | 185 | |
10005 | APPLE | 7/5/2013 | 95 | 250 | |
10006 | GRAPE | 9/5/2013 | 150 | 185 | |
10007 | ORANGE | 9/5/2013 | 60 | 500 | |
10008 | ORANGE | 10/5/2013 | 130 | 500 | |
10009 | GRAPE | 13/5/2013 | 50 | 185 | B200 |
10010 | GRAPE | 13/5/2013 | 50 | 185 | B300 |
Note, total inventory comes in on every order line, but is specific to a product, so there's 250 units of apple in the total inventory.
If a order has an allocated batch number, then it's considered allocated.
Orders are sorted by date.
The new calculated field will be called 'Achievable', and values will be 'Sufficient Stock' and 'Insufficient Stock'
By default, anything that has been allocated is achievable.
For anything else, the order is acheivable if:
the total inventory minus total allocated stock minus all unallocated orders for that item that occur before the current order is greater than the order quantity of that line.
Some examples to explain the logic:
For Order 10005:
Product = Apple
Order Qty = 95
Total Inventory of Apple = 250
Total Allocated Apple = 143
Total unallocated orders of apple that come before order 10005 = 100
Effectively, there's 250 - 143 - 100 = 7 apple available, and therefore order 10005 cannot be fufilled.
For order 10004:
Product = Grape
Order Qty = 35
Total inventory of grape = 185
Total allocated grape = 100
Total unallocated orders for grape that come before order 10004 = 0
Effectively there's 185 - 100 - 0 = 85 grape available, therefore order 10004 can be fufilled.
I hope i posted up enough information for someone to understand what i'm trying to achieve.
Thanks in advance for any assistance.
John
Hi John,
First of all if you are using sql statement then plz use functions like ROLLUP() and you can achieve this structure in sql only and if you want it to be done in Qlikview then use group by in load.
Hi Son of Sardar,
Thanks for your input.
I am not using SQL, just qlikview.
I have tried grouping, however that only gives me the total. Which is fine for totalling allocated stock, but I haven't been successful in getting it to do the cumulative total of orders for a product that preceed this order. I suspect some sort of range sum may be the answer, but I'm inexperienced in using that function.
John.
If you are searching for cummalative sum then go to this sample well may it help you.
you can use rangesum() function as
RangeSum(Sales,peek(date ),date)