topic Re: Rangesum() in running total Help. in QlikView

Rangesum() in running total Help.

aniketsr — Tue, 17 Jan 2017 09:42:20 GMT

Hi Team,

Iam using rangesum and above function and plotting running total against (ID,Year,month).

Consider we have Year as 2015 and 2016.

If we select both the years the running total against the month is coming perfect,

But once i select 2016 the running total get affected as the rows get reduced for 2016.

i'am using the below expression :

=RangeSum(Above(TOTAL Sum({<MONTH=,YEAR=>}Amount),0,12))

Can someone guide me for an expression which should give static result against the respective month even after we select 2016.

Thank You.

Scripting‌

QlikView App Development‌

New to QlikView‌

Re: Rangesum() in running total Help.

MK_QSL — Tue, 17 Jan 2017 09:58:58 GMT

use this

Calculating rolling n-period totals, averages or other aggregations

Re: Rangesum() in running total Help.

tresB — Tue, 17 Jan 2017 10:02:02 GMT

You have to perhaps use Aggr() additionally with another Sum(). Could you post your sample qvw for detailed and specific help? You can also try yourself reading this blog post: Calculating rolling n-period totals, averages or other aggregations

Re: Rangesum() in running total Help.

aniketsr — Tue, 17 Jan 2017 10:08:03 GMT

Hi Manish,

I used the same link to implement it ,the values are coming perfect if i use the rangesum function accordingly.

the initial expression was as below

=Sum(aggr(rangesum(above(TOTAL sum({<MONTH=Year=>}Amount),0,12)),ID,Year,MONTH))

But as i have negative values in between hence the range is not coming proper so i modified the expression to

=RangeSum(Above(TOTAL Sum({<MONTH=,YEAR=>}Amount),0,12)) using this expression the running total values came perfect but now when i select 2016 the running total gets affected.

Re: Rangesum() in running total Help.

MK_QSL — Tue, 17 Jan 2017 10:09:29 GMT

Provide sample data or sample application. Your explanation would not enough for us to test.

Re: Rangesum() in running total Help.

aniketsr — Tue, 17 Jan 2017 11:45:12 GMT

A quick doubt,

can i use a Column name in the below expression:

=Sum(aggr(rangesum(above(TOTAL sum({<MONTH=Year=>}Amount),0,12)),ID,Year,MONTH))

like Sum(aggr(rangesum(above(TOTAL sum({<MONTH=Year=>}Column(1)),0,12)),ID,Year,MONTH))

because we have a lot of addition and subtraction in the logic of the column for which we are calculating rolling 12

Re: Rangesum() in running total Help.

tresB — Tue, 17 Jan 2017 11:50:10 GMT

Re: Rangesum() in running total Help.

aniketsr — Tue, 17 Jan 2017 12:26:13 GMT

Can you suggest me a way i can implement this ,if i have a lot of subtraction and addition in the expression

like :

sum(a)+

sum({<type={'abc'}>}B)

(Sum({<Type={'xyz'}>}C)

sum({<Type-={'xyz'}>}D)

)

and help would be appreciated

additionally i want to implement something like the below link :

https://community.qlik.com/message/1193775#1193775

Re: Rangesum() in running total Help.

sunny_talwar — Tue, 17 Jan 2017 12:45:55 GMT

Would you be able to share a sample of what you have so far?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Rangesum() in running total Help.

aniketsr — Wed, 18 Jan 2017 07:05:16 GMT

Hi Team,

Attached is the Sample Application to implement rolling 12

gwassenaar‌Scripting‌QlikView App Development‌

Re: Rangesum() in running total Help.

aniketsr — Wed, 18 Jan 2017 08:08:32 GMT

Hi Sunny,

Attached is the sample application.

Re: Rangesum() in running total Help.

aniketsr — Wed, 18 Jan 2017 08:09:42 GMT

Hi Tresesco,

Attached is the sample copy

Re: Rangesum() in running total Help.

tresB — Wed, 18 Jan 2017 08:34:30 GMT

Try removing TOTAL from your expression like:

Sum(aggr(rangesum(above( sum({<Month=,Year=>}Actual),0,12)),ID,Year,Month))

Re: Rangesum() in running total Help.

sunny_talwar — Wed, 18 Jan 2017 10:14:42 GMT

Is this what you are looking for? Even when you select 2015 or 2016, the rolling 12 month expression stays intact?

If not, then I think it would be helpful for you to point out the result you are expecting to see.

Re: Rangesum() in running total Help.

aniketsr — Thu, 19 Jan 2017 06:31:11 GMT

Hi Sunny,

i have uploaded the sample wherein i calculate the rolling 12 months , but when i select 2016&2015 the rolling 12 month

functions properly.On the other hand when i select 2016 the rolling 12 month functionality does not work.

Please guide me on the issue

Thanks,

Re: Rangesum() in running total Help.

aniketsr — Thu, 19 Jan 2017 07:09:06 GMT

Hi Tresesco,

thanks for the help i tried but did not work

i have uploaded the sample wherein i calculate the rolling 12 months , but when i select 2016&2015 the rolling 12 month

functions properly.On the other hand when i select 2016 the rolling 12 month functionality does not work.

Please guide me on the issue

Re: Rangesum() in running total Help.

sunny_talwar — Thu, 19 Jan 2017 10:10:48 GMT

Aniket - I am still not sure what you are looking for brother The only thing I see different when I select 2016 is that I only see the rows where Year is 2016. Is this what your issue is? You want to continue seeing 2015 and 2016 even when a single year is selected? I am not seeing any other issue with what you have (unless I am missing something here)

Re: Rangesum() in running total Help.

aniketsr — Thu, 19 Jan 2017 10:26:49 GMT

Hi sunny,

Ok i will explain properly

the running total changes after the year changes in the attached sample i want to have the rolling sum on the ID.

But if i select any month or year the straight table should get reduced to that Year and month respectively and the running total should be the same

Example:for R7918 the running sum should not initiate again to 15801 which is nothing but the actual number for jan'16 but it should be the Sum of feb-15 to jan'16 and on selecting 2016 the value in running 12 should be the sum of feb'15 to jan'16.

Thanks

Re: Rangesum() in running total Help.

sunny_talwar — Thu, 19 Jan 2017 10:44:15 GMT

Do you have QV12? If you do, checkout the attached file. this isn't perfect, but let's see if we are moving in the right direction

Re: Rangesum() in running total Help.

aniketsr — Thu, 19 Jan 2017 10:53:43 GMT

Hi,

Yes I do have QV 12 but there is no value in the "Actual Numerator Rolling 12" Column.