# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Did you mean:
Contributor III

## Sumifs using date and dimension

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?

Labels (4)

• ### sumifs

1 Solution

Accepted Solutions
MVP

see attached

14 Replies
Specialist

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.

MVP

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)``
Contributor III
Author

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?

MVP

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

Contributor III
Author

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
MVP

noconcatenate

resident Table

order by WW_Item_Number__SDLITM,Promised_Ship_Date__SDPPDJ;

drop table Table;

Contributor III
Author

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

MVP

do you want to calculate consumed and available both?

Contributor III
Author

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