rangesum is ignoring zero values

[aniketsr]

Hi

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

[senpradip007]

Could you please upload sample qvw?

[aniketsr]

Hi Pradip ,

Attached is the Sample App

[aniketsr]

Hi all ,

Is there any solution for this in qlik.

Qlik India‌stalwar1‌kush141087‌

[sunny_talwar]

Can you point out where it is zero?

[aniketsr]

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.

[sunny_talwar · Accepted solution]

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

[Or]

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.