Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)))
Would you be able to share a sample where you see this issue?
Is Rangesum (DmDQty,SupplyQty) not giving the desired output?
Not sure what is the problem you are pointing out to
I would assume something like this
RangeSum(Above(RangeSum(Sum(Supply_Qty), -Sum(DmdQty)), 0, RowNo()))
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.
Or this
RangeSum(Above(RangeSum(Supply_Qty, -DmdQty), 0, RowNo()))
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.
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)))
Hi! Sunny,
It finally works... after spending long hours working on this.
Thank you so much...
Rgds
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.