topic Seperating Positive and Negative Values in QlikView

Seperating Positive and Negative Values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Seperating Positive and Negative Values

HirisH_V7 — Fri, 27 May 2016 07:57:57 GMT

Hi,

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

-Hirish

Re: Seperating Positive and Negative Values

sunny_talwar — Fri, 27 May 2016 09:08:10 GMT

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

Re: Seperating Positive and Negative Values

marcus_sommer — Fri, 27 May 2016 11:17:16 GMT

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