topic Re: Complex Count Formula Help Needed in QlikView

Complex Count Formula Help Needed

JustinRamsey — Tue, 31 Aug 2021 12:24:16 GMT

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

Re: Complex Count Formula Help Needed

stevejoyce — Tue, 31 Aug 2021 12:35:32 GMT

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.

Re: Complex Count Formula Help Needed

JustinRamsey — Tue, 31 Aug 2021 12:41:13 GMT

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

Re: Complex Count Formula Help Needed

stevejoyce — Tue, 31 Aug 2021 12:42:18 GMT

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)

Re: Complex Count Formula Help Needed

JustinRamsey — Tue, 31 Aug 2021 12:44:28 GMT

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