Seperating Positive and Negative Values

[Anonymous]

Hi,

I was wondering what the best way would be to split data based on Positive and Negative values.

For example.

I have added an expression to a pivot table which uses data from 3 or more columns to do a calculation.

If there is a negative value in this column, i want to remove it and move the values into another column and then sum it.

and leave the positive values in the existing column (without the negative values) to sum the total.  (the negative- and positive values reflect different products quantities)

See below :

     Column 1       Column2   Column 3

     10                    10

     20                    20

    -30                                   -30

     50                    50

     40                    40

The expression is below :

sum(QTY)-sum(NISTM1/OUOM_QTY)-sum(PAL_QTY)          //This results in positive and negative numbers that i want to split

Is there a way to apply some sort of filter on the front-end or will i be able to use either an IF statement or Flags.  If so, how would i put that in practice. 

Please assist if possible.

[HirisH_V7]

Hi,

Can you provide Sample data with required out put in excel.It  will be easy to address.

-Hirish

[sunny_talwar]

I think you can try it with statement:

Positive

If(RangeSum(Sum(QTY), -Sum(NISTM1/OUOM_QTY), -Sum(PAL_QTY)) > 0,  RangeSum(Sum(QTY), -Sum(NISTM1/OUOM_QTY), -Sum(PAL_QTY))

Negative

If(RangeSum(Sum(QTY), -Sum(NISTM1/OUOM_QTY), -Sum(PAL_QTY)) < 0,  RangeSum(Sum(QTY), -Sum(NISTM1/OUOM_QTY), -Sum(PAL_QTY))

[marcus_sommer]

You could create a simply flag within the script, like:

if(Column>= 0, 'Positive', 'Negative') as Flag // or in a numeric way if(Column>= 0, 0, 1)

and use this flag then as set analysis condition, like:

sum({< Flag = {'positive'}>} QTY)

whereby you didn't need such flag mandatory then you could also query:

sum({< Column = {">=0"}>} QTY)

or if it's not direct a field-value else the result from an aggregation then:

sum({< Column = {"=sum(Column)>=0"}>} QTY)

- Marcus