Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinRamsey
Creator
Creator

Complex Count Formula Help Needed

Good Day, 

I am needing assistance with a rather tricky formula. I have a formula that counts the number of order lines (%Key_POSNR) that have parts with out safety stock allocated to them and have an associated missing parts flag (RESB.XFEHL=X). That formula is below and working 

Count({<Safety_Stock={'No Safety Stock'}, RESB.XFEHL={X}>} distinct(%KEY_POSNR))

What I am needing now is the order lines impacted by this since we usually only ship complete orders. So I need a formula that will count the total order lines on any order that contains an order line with the above criteria. For example order 1456 has 5 lines but only one of them has a missing non stock part, we would want this formula to return 5 so we can capture the total number of lines affected by the missing part. Any help would be appreciated! Fields for reference

VBAK.VBELN= Order Number

%KEY_POSNR=Order Line

Safety_Stock=Stock Indicator

RESB.XFEHL= Missing Parts Flag

1 Solution

Accepted Solutions
stevejoyce
Specialist II
Specialist II

I think a typo on my part, try this...

Count({<VBAK.VBELN={"=Count({<Safety_Stock={'No Safety Stock'}, RESB.XFEHL={X}>} %KEY_POSNR)>0"} >} %KEY_POSNR)

View solution in original post

4 Replies
stevejoyce
Specialist II
Specialist II

Count({<VBAK.VBELN={"=Count({<Safety_Stock={'No Safety Stock'}, RESB.XFEHL={X}>} %KEY_POSNR)>0"} %KEY_POSNR)

I think you're looking for something like above.  I am counting all Order Lines but limited to Order Numbers that have at least 1 line that meet that criteria.

JustinRamsey
Creator
Creator
Author

I am getting an error in set modifier expression from that formula Stevejoyce

 

stevejoyce
Specialist II
Specialist II

I think a typo on my part, try this...

Count({<VBAK.VBELN={"=Count({<Safety_Stock={'No Safety Stock'}, RESB.XFEHL={X}>} %KEY_POSNR)>0"} >} %KEY_POSNR)

JustinRamsey
Creator
Creator
Author

I think that got it! I really really appreciate the assistance on it!