Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cacosta35
Creator II
Creator II

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 II
Champion II

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

cacosta35
Creator II
Creator II
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()))

cacosta35
Creator II
Creator II
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)))

View solution in original post

cacosta35
Creator II
Creator II
Author

Hi!  Sunny,

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

Thank you so much...

Rgds

cacosta35
Creator II
Creator II
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.