Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
nburton78
Contributor III
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 NumberQty on Handorder quantityPromised ship dateconsumed
Part A50101/1/20200
Part A50102/1/202010
Part A50103/1/202020
Part B30101/1/20200
Part B30102/1/202010
Part B30103/1/202020
Part B30104/1/202030

 

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?  

1 Solution

Accepted Solutions
Kush
MVP
MVP

14 Replies
chrismarlow
Specialist
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;

20200725_1.png

Cheers,

Chris.

Kush
MVP
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)
nburton78
Contributor III
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__SDLITMPromised_Ship_Date__SDPPDJQuantity_Ordered__SDUORGConsumedExpected 
BGWA000030Q016/4/2020 0:00198179810
BGWA000030Q016/29/2020 0:00600001981

 

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?

Kush
MVP
MVP

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

 

nburton78
Contributor III
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__SDLITMPromised_Ship_Date__SDPPDJQuantity_Ordered__SDUORGConsumedExpected
BGWA000030Q016/4/2020 0:00198100
BGWA000030Q016/29/2020 0:00600079811981
Kush
MVP
MVP

change load order to 

 noconcatenate

load *

resident Table

order by WW_Item_Number__SDLITM,Promised_Ship_Date__SDPPDJ;

drop table Table;

nburton78
Contributor III
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.  

OrderPart #DueOrder QtyQty on HandConsumedAvailablecan order be  filled
1Part A1/1/20201040040Yes
2Part A2/1/202010401030Yes
3Part A3/1/202010402020Yes
4Part A4/1/202010403010Yes
5Part A5/1/20201040400No

 

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

Kush
MVP
MVP

do you want to calculate consumed and available both?

nburton78
Contributor III
Contributor III
Author

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