Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am working in a a straight table and I need help figuring out how to make the last column in Qlikview. I have created the table in excel using the following formula:
=SUMIFS(C:C,A:A,A2,D:D,"<"&D2)
sum order quantity if it is the same part number and the Promised ship date is less than given promised ship date
Part Number | Qty on Hand | order quantity | Promised ship date | consumed |
Part A | 50 | 10 | 1/1/2020 | 0 |
Part A | 50 | 10 | 2/1/2020 | 10 |
Part A | 50 | 10 | 3/1/2020 | 20 |
Part B | 30 | 10 | 1/1/2020 | 0 |
Part B | 30 | 10 | 2/1/2020 | 10 |
Part B | 30 | 10 | 3/1/2020 | 20 |
Part B | 30 | 10 | 4/1/2020 | 30 |
The formula I have in Qlikview currently only sums the order quantity based on the part number:
sum(Total <WW_Item_Number__SDLITM> Quantity_Ordered__SDUORG)
How would I add the date aspect?
Hi,
Shamelessly cribbing from this post, as I try to 'upskill' from QlikView to Qlik Sense, try;
rangesum( above( sum([order quantity]),0,rowno()-1))
Or something similar to do the accumulation;
Cheers,
Chris.
Just to modify little bit to what @chrismarlow suggested. If you select the Promise ship date, Numbers will change and to correct that you can use below
sum(aggr(rangesum( above(sum(Total <WW_Item_Number__SDLITM> {<[Promised ship date]>}Quantity_Ordered__SDUORG),0,rowno()-1)),WW_Item_Number__SDLITM,[Promised ship date]))*avg(1)
The second post is very close, it seems like if it is the earliest promised ship date it is summing the order quantity. If it is the earliest ship date it should not sum the order qty, the next promised ship date should sum the order qty of the previous orders. The "consumed" column is what the formula calculated, the "Expected" is the sum using the excel formula.
WW_Item_Number__SDLITM | Promised_Ship_Date__SDPPDJ | Quantity_Ordered__SDUORG | Consumed | Expected |
BGWA000030Q01 | 6/4/2020 0:00 | 1981 | 7981 | 0 |
BGWA000030Q01 | 6/29/2020 0:00 | 6000 | 0 | 1981 |
Do you think it could be due to the load order? I was reading another article that said "if you are using the advanced aggregation (aggr() function), you'll need to ensure, that MONTH field values load order is chronological ascending, e.g. by loading a master calendar first. The aggr() function's dimension values will always be ordered in load order (not regarding any order you may define for the chart)." Should I do that with the Promised ship date field?
I think you are looking for same WW_Item_Number__SDLITM. Is it not the earliest for New WW_Item_Number__SDLITM?
I changed the load order and now the consumed quantities are on the right lines, but its still summing the previous line and the current line, it should only sum the previous line(s) per WW part number
like this:
WW_Item_Number__SDLITM | Promised_Ship_Date__SDPPDJ | Quantity_Ordered__SDUORG | Consumed | Expected |
BGWA000030Q01 | 6/4/2020 0:00 | 1981 | 0 | 0 |
BGWA000030Q01 | 6/29/2020 0:00 | 6000 | 7981 | 1981 |
change load order to
noconcatenate
load *
resident Table
order by WW_Item_Number__SDLITM,Promised_Ship_Date__SDPPDJ;
drop table Table;
nope still calculating wrong. Maybe it will help to explain what i'm trying to do. I am trying to figure out how many orders can be fulfilled with the quantity on hand so say we have 5 orders for 10 pieces and 40 pieces on hand. I can fulfill the first 4 orders with the qty on hand. so I am creating the consumed column to create a hypothetical usage, if I use 10 on the first order how many do I have left and so on.
Order | Part # | Due | Order Qty | Qty on Hand | Consumed | Available | can order be filled |
1 | Part A | 1/1/2020 | 10 | 40 | 0 | 40 | Yes |
2 | Part A | 2/1/2020 | 10 | 40 | 10 | 30 | Yes |
3 | Part A | 3/1/2020 | 10 | 40 | 20 | 20 | Yes |
4 | Part A | 4/1/2020 | 10 | 40 | 30 | 10 | Yes |
5 | Part A | 5/1/2020 | 10 | 40 | 40 | 0 | No |
Consumed is calculated by summing the order qty where the part number is the same and the Due date is less than the line's due date.
Available is the qty on hand minus the consumed
do you want to calculate consumed and available both?
Yes I was going create another column where I subtract consumed from qty on hand.