Remove negative value rows and from total column in a pivot table

[MikeF]

Hello All,

I have a pivot table that I need to remove the negative value rows and also remove them from the Qlik generated Total column.  I am using this measure expression I found on the board and it seems to work as brings in the negatives.  Thanks/Mike

=if (sum(RangeSum([Estimate$], -RangeMax(0,[Invoice$])))>0, Sum([Estimate$])-Sum([Invoice$]))

[rubenmarin]

Hi, so your expression is  Sum([Estimate$])-Sum([Invoice$])? and you want to remove negative values at what level?

If it's for the 2 dimensions it will be easier, just:

Sum(Aggr(If(Sum([Estimate$])-Sum([Invoice$])>0,Sum([Estimate$])-Sum([Invoice$])),[Job Draw Phase],[Homeowner/Job #]))

[MikeF]

Yes at dimension level. So that only the $3000 is what remains. 

I changed the measure expression to this,  Sum(Aggr(If(Sum([Estimate$])-Sum([Invoice$])>0,Sum([Estimate$])-Sum([Invoice$])),[Job Draw Phase],[Homeowner/Job #])) and now I get a blank screen with no amts.

Do I need this expression in the 2 dimension fields?

Thanks.

[rubenmarin]

It also needs the vertical dimension:

 Sum(Aggr(If(Sum([Estimate$])-Sum([Invoice$])>0,Sum([Estimate$])-Sum([Invoice$])),[Job Draw Phase],[Homeowner/Job #],[Draw Months)))

You can first try If(Sum([Estimate$])-Sum([Invoice$])>0,Sum([Estimate$])-Sum([Invoice$]))

and when it returns something just include in an sum(aggr([expression],[AllDimensionsSeparatedByComma]))

[MikeF]

I tried this in the measure and I get a blank table too.

=Sum(Aggr(If(Sum([Estimate$])-Sum([Invoice$])>0,Sum([Estimate$])-Sum([Invoice$])),[Contract Phase.Phase],([Contract.Homeowner] & ' ' & [Job#],[Target.autoCalendar.YearMonth])))

I tried this  If(Sum([Estimate$])-Sum([Invoice$])>0,Sum([Estimate$])-Sum([Invoice$])) and I get the following,  it removed the negative which is perfect, but left it in the Qlik TOTALS column.

[rubenmarin · Accepted solution]

If those are the dimensions used on the table, the syntax should be:

=Sum(Aggr(If(Sum([Estimate$])-Sum([Invoice$])>0,Sum([Estimate$])-Sum([Invoice$])),[Contract Phase.Phase],[Contract.Homeowner], [Job#],[Target.autoCalendar.YearMonth]))

Comma instead of & ' ' & and removed extra parenthesys in the dimenions list.

[MikeF]

Thanks for helping with this.  I spent hours on this one glitch left in the sheet.