topic Set Analysis Issue using Sum in QlikView

Set Analysis Issue using Sum

principal — Sat, 16 Nov 2024 00:52:57 GMT

Hi All,

I have the following expression, I basically want a table where the Pallet Expected Qty is less than the Stock Cases, so it's not a full pallet of stock

NUM(SUM({<[Product Pick Type] = {'PALLET'},[Product Cases Per Pallet] = {"=[Product Cases Per Pallet]) > SUM([Stock Cases])"}>}[Stock Cases]),$(format.number))

Can anyone suggest why the above expression is not returning the correct result, I would have thought the above would have worked

Thanks.

Re: Set Analysis Issue using Sum

agigliotti — Wed, 08 Apr 2020 08:55:19 GMT

maybe this:
=NUM( SUM( {< [Product Pick Type] = {'PALLET'},
[Product Cases Per Pallet] = {">$(=Sum([Stock Cases]))"} >} [Stock Cases] ),
$(format.number) )

i hope it helps.

Re: Set Analysis Issue using Sum

Anil_Babu_Samineni — Wed, 08 Apr 2020 09:01:28 GMT

Please remove red part from below and then try?

NUM(SUM({<[Product Pick Type] = {'PALLET'},[Product Cases Per Pallet] = {"=[Product Cases Per Pallet]) > SUM([Stock Cases])"}>}[Stock Cases]),$(format.number))

Re: Set Analysis Issue using Sum

principal — Wed, 08 Apr 2020 09:07:01 GMT

Hi,

Thanks for that, however that only returns 1 record in the table, there are 120 records that need to be returned.

Regards

Alan.

Re: Set Analysis Issue using Sum

Anil_Babu_Samineni — Wed, 08 Apr 2020 09:10:59 GMT

I would suggest start testing step-step

1) Sum([Stock Cases])

2) Sum({<[Product Pick Type]={'PALLET'}>} [Stock Cases])

3) Please validate manually from 1st point and adding the measure 2nd point

4) Please check manual entry for data point in table?

Re: Set Analysis Issue using Sum

principal — Wed, 08 Apr 2020 09:17:50 GMT

Hi,

I have done that, as you can see from the screenshot below, it only returns the first value of 30, but all values should show as the qty's are less than the Pallet Qty

Re: Set Analysis Issue using Sum

Anil_Babu_Samineni — Wed, 08 Apr 2020 09:21:17 GMT

Please attach QVW with sample data set to check?

Re: Set Analysis Issue using Sum

principal — Wed, 08 Apr 2020 09:53:50 GMT

see the attached file, thanks

Re: Set Analysis Issue using Sum

Anil_Babu_Samineni — Wed, 08 Apr 2020 10:21:28 GMT

Perhaps this?

SUM({<[Product Pick Type] = {'PALLET'}, [Product Cases Per Pallet]={"=Sum([Product Cases Per Pallet])>=Sum([Stock Cases])"}>} [Stock Cases])

Re: Set Analysis Issue using Sum

principal — Wed, 08 Apr 2020 11:04:49 GMT

That's it, thanks for your help