Set Analysis Issue using Sum

[principal]

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.

[agigliotti]

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

i hope it helps.

[Anil_Babu_Samineni]

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))

[principal]

Hi,

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

Regards

Alan.

[Anil_Babu_Samineni]

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?

[principal]

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

[Anil_Babu_Samineni]

Please attach QVW with sample data set to check?

[principal]

see the attached file, thanks

[Anil_Babu_Samineni · Accepted solution]

Perhaps this?

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

[principal]

That's it, thanks for your help