Sign depending Sum(): Finding the Totals of Positive or Negative SubParcels

[#1]

Dear all,

I'm processing a table with the following structure:

ENTITY is linked to PARCEL_CODE, and PARCEL_CODE holds a VALUE over TIME

Given a Table with ENTITY and TIME as Dimensions, to find the aggregated TOTAL of an ENTITY at a TIME is a rather simple Sum(VALUE) which works fine since Qlikview figures what PARCEL_CODE's VALUEs should be included in the sum.

My problem is calculating the Total of an ENTITY considering only the positive (or negative) PARCEL_CODE's VALUEs .

Something like:

TOTAL of POSITIVES =IF(Sum(VALUE)>0, Sum(Value),0)

doesn't work, because it evaluates the sum of all the PARCELs not the Sum of the parcels that have an aggregated positive value.

Any suggestions will be highly appreciated, please !!

Thanks in advance,

pmb

[#2]

Try

sum( If( Value > 0 , Value , 0) )

[#3]

Not really an option, since we need to know the accumulated sum of the parcels over time, not the instantaneous value.

Regards,

pmb

[pover · Accepted solution]

Maybe try the following:

sum(aggr(IF(Sum(VALUE)>0, Sum(Value),0) ,PARCEL_CODE))

Regards.