topic Re: Sumifs using date and dimension in QlikView

Sumifs using date and dimension

nburton78 — Sat, 16 Nov 2024 00:16:12 GMT

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?

Re: Sumifs using date and dimension

chrismarlow — Sat, 25 Jul 2020 11:21:04 GMT

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.

Re: Sumifs using date and dimension

Kushal_Chawda — Sat, 25 Jul 2020 11:54:15 GMT

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)

Re: Sumifs using date and dimension

nburton78 — Tue, 04 Aug 2020 15:22:24 GMT

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?

Re: Sumifs using date and dimension

Kushal_Chawda — Tue, 04 Aug 2020 15:17:18 GMT

I think you are looking for same WW_Item_Number__SDLITM. Is it not the earliest for New WW_Item_Number__SDLITM?

Re: Sumifs using date and dimension

nburton78 — Tue, 04 Aug 2020 15:51:06 GMT

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

Re: Sumifs using date and dimension

Kushal_Chawda — Tue, 04 Aug 2020 15:59:34 GMT

change load order to

noconcatenate

load *

resident Table

order by WW_Item_Number__SDLITM,Promised_Ship_Date__SDPPDJ;

drop table Table;

Re: Sumifs using date and dimension

nburton78 — Tue, 04 Aug 2020 16:55:37 GMT

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

Re: Sumifs using date and dimension

Kushal_Chawda — Tue, 04 Aug 2020 17:00:06 GMT

do you want to calculate consumed and available both?

Re: Sumifs using date and dimension

nburton78 — Tue, 04 Aug 2020 17:04:09 GMT

Yes I was going create another column where I subtract consumed from qty on hand.

Re: Sumifs using date and dimension

Kushal_Chawda — Tue, 04 Aug 2020 17:08:24 GMT

Based on your logic expression provided should work fine. In this case would you be able to share sample data or app?

Re: Sumifs using date and dimension

nburton78 — Tue, 04 Aug 2020 17:30:30 GMT

Here's a sample, its summing the total qty each time and it only needs to sum the previous order line.

Re: Sumifs using date and dimension

Kushal_Chawda — Tue, 04 Aug 2020 20:07:46 GMT

see attached

Re: Sumifs using date and dimension

nburton78 — Tue, 04 Aug 2020 20:14:12 GMT

Thank you so much this works perfectly! Can you explain what you did?

Re: Sumifs using date and dimension

Kushal_Chawda — Tue, 04 Aug 2020 20:22:58 GMT

I just removed total keyword from sum().