topic Re: Sum of only negative values in App Development

Sum of only negative values

okin_pfa — Thu, 03 Nov 2022 07:28:04 GMT

Hi,

Can somebody help me to sum only negative values ?

In first two columns I have [Stock Qty] and [Backorders Total] . Third, blue column rba is difference of the [Stock Qty] and [Backorders Total].
I would like to sum only negative values of rba and in total row to sum it per ItemNo which is my second dimension in Table.

Re: Sum of only negative values

BrunPierre — Wed, 02 Nov 2022 09:54:17 GMT

Sum({<Customer={"=Sum([Stock Qty] -[Backorders Total])<0"}>}[Stock Qty] -[Backorders Total])

Re: Sum of only negative values

MarcoWedel — Wed, 02 Nov 2022 17:30:50 GMT

Sum(RangeMin([Stock Qty]-[Backorders Total],0))

Re: Sum of only negative values

robert99 — Wed, 02 Nov 2022 19:42:49 GMT

Hi @okin_pfa

Im not sure exactly what you want.

I would like to sum only negative values of rba.

By PartNo or by Customer . I assume by PartNum (as you have used PartNo in your 4th column)? Or by both or just Customer but also gives the correct total by PartNo

And have tried if instead of Set Analysis.

sum(aggr(

if(Sum(rbo)<0,sum(rbo),0)

,Customer,ItemNo)) //Or just ItemNo or Customer. By Customer should give you the correct total as per the blue column

Re: Sum of only negative values

SunilChauhan — Thu, 03 Nov 2022 07:30:26 GMT

Inaddition to MarcoWedel

Reply You can use Dimensionality()

If(Dimensionality()=0, Sum(RangeMin([Stock Qty]-[Backorders Total],0)),Sum([Stock Qty]-[Backorders Total]))

Hope this Helps