RangeSum with Missing Values

[Anonymous]

Hello!  I hope someone can help me on this.

I have a pivot table with missing values  for 2 fields DmdQty and Supply Qty...  My objective is to do a rangesum  based on Supply -demand...but it's giving me empty cells.

Thank you in advance.

[sunny_talwar]

Would you be able to share a sample where you see this issue?

[vinieme12]

Is Rangesum (DmDQty,SupplyQty) not giving the desired output?

Not sure what is the problem you are pointing out to

[sunny_talwar]

I would assume something like this

RangeSum(Above(RangeSum(Sum(Supply_Qty), -Sum(DmdQty)), 0, RowNo()))

[Anonymous]

hi!  Vinneth,

I haven't gone in the rangesum part yet.  I just tried doing Supply Qty - Dmd Qty  and it's giving  empty or null values in the supply bal column. Thus , I know it's because of the missing values...Is there anyway to convert them to 0?  I tried doing if(Isnull(Supplyqty,0,SupplyQty) but doesn't work.  and also tried If(Len(Trim(Supply_Qty)) > 0, Supply_Qty,0)  to no avail.

Tks.

[sunny_talwar]

Or this

RangeSum(Above(RangeSum(Supply_Qty, -DmdQty), 0, RowNo()))

[Anonymous]

hi!  Sunny,

The  formula only  combine combines 2 columns into  1column making demand qty into negatives ...I can actually do another expression field to do a rangesum  based on this new field but is there anyway to do it just one expression field?  I'm planning to do an N print report and prefer not to see the rangesum field.

Tks.

[sunny_talwar · Accepted solution]

It seems that you might have more than one dimension in your chart.... can you try one of these

RangeSum(Above(TOTAL RangeSum(Supply_Qty, -DmdQty), 0, RowNo(TOTAL)))

or

RangeSum(Above(TOTAL RangeSum(Sum(Supply_Qty), -Sum(DmdQty)), 0, RowNo(TOTAL)))

[Anonymous]

Hi!  Sunny,

It finally works... after spending long  hours working on this.

Thank you so much...

Rgds

[Anonymous]

hi!  Vinneth,

I tried this but it was just  combining the 2 columns into 1...as what Sunny pointed out, 'coz probably I have more than one dimension in my chart.

Appreciate your time working on this.

Tks.