Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

RangeSum with Missing Values

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.

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

9 Replies
sunny_talwar

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

vinieme12
Champion III
Champion III

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

Not sure what is the problem you are pointing out to

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

I would assume something like this

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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

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
Not applicable
Author

Hi!  Sunny,

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

Thank you so much...

Rgds

Anonymous
Not applicable
Author

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.