topic rangesum is ignoring zero values in QlikView

rangesum is ignoring zero values

aniketsr — Fri, 05 May 2017 07:49:30 GMT

Iam using rangesum function to calculate the rolling 12 months but wherever there is zero it showing 0 in the output and skipping the row

iam using the below expression :

sum(aggr(RangeSum(Above(

Sales

,0,12)),SR#,Year,MONTH_SHORT_NAME))

Re: rangesum is ignoring zero values

senpradip007 — Fri, 05 May 2017 07:55:51 GMT

Could you please upload sample qvw?

Re: rangesum is ignoring zero values

aniketsr — Fri, 05 May 2017 09:04:38 GMT

Hi Pradip ,

Attached is the Sample App

Re: rangesum is ignoring zero values

aniketsr — Tue, 13 Jun 2017 14:31:37 GMT

Hi all ,

Is there any solution for this in qlik.

Qlik India‌stalwar1‌kush141087‌

Re: rangesum is ignoring zero values

sunny_talwar — Tue, 13 Jun 2017 14:42:49 GMT

Can you point out where it is zero?

Re: rangesum is ignoring zero values

aniketsr — Tue, 13 Jun 2017 14:51:14 GMT

Hi Sunny,

Thank you for your prompt response.

For Dec 2016 the rolling sum column should have value as 0.97 as the actual value is 0.00, but we are getting 0.00 in the rolling sum column.

I have attached the snapshot and also highlighted the transaction where the issue exist.

Re: rangesum is ignoring zero values

sunny_talwar — Tue, 13 Jun 2017 14:59:30 GMT

There is no data for

SGP# = E9678

ActivityType = Client Financing or Derivative BalanceSheet or MT_Finance

intYearMonth = 201612

May be you need to create a dummy value in the script

Generating Missing Data In QlikView

Re: rangesum is ignoring zero values

Or — Tue, 13 Jun 2017 15:08:39 GMT

Hello,

RangeSum() isn't ignoring zero values. Your issue is with the set analysis used within - it seems to be blocking your values (as you can see by simply removing the "ActivityType" condition).

I'm not sure if this would work for you, but you could try using something along the lines of:

sum({<Year,MONTH_SHORT_NAME>}Expense/

if(ActivityType = 'Client Financing' OR ActivityType = 'MT_Finance' OR ActivityType = 'Derivative BalanceSheet',1000)

)

This seems to show the moving sum even when the specific line is 0, but it may have other issues - I didn't look into it enough to tell.

Good luck.