topic Re: RangeSum with Missing Values in QlikView

RangeSum with Missing Values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: RangeSum with Missing Values

sunny_talwar — Fri, 07 Apr 2017 17:44:01 GMT

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

Re: RangeSum with Missing Values

vinieme12 — Fri, 07 Apr 2017 17:53:55 GMT

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

Not sure what is the problem you are pointing out to

Re: RangeSum with Missing Values

sunny_talwar — Fri, 07 Apr 2017 18:00:17 GMT

I would assume something like this

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

Re: RangeSum with Missing Values

Anonymous — Fri, 07 Apr 2017 18:01:42 GMT

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.

Re: RangeSum with Missing Values

sunny_talwar — Fri, 07 Apr 2017 18:03:08 GMT

Or this

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

Re: RangeSum with Missing Values

Anonymous — Fri, 07 Apr 2017 18:39:17 GMT

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.

Re: RangeSum with Missing Values

sunny_talwar — Fri, 07 Apr 2017 18:41:49 GMT

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

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

Re: RangeSum with Missing Values

Anonymous — Fri, 07 Apr 2017 20:36:08 GMT

Hi! Sunny,

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

Thank you so much...

Rgds

Re: RangeSum with Missing Values

Anonymous — Fri, 07 Apr 2017 20:38:46 GMT

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.