Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aniketsr
Creator
Creator

Rangesum() in running total Help.

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

37 Replies
tresesco
MVP
MVP

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

aniketsr
Creator
Creator
Author

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.

MK_QSL
MVP
MVP

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

aniketsr
Creator
Creator
Author

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

tresesco
MVP
MVP

No

aniketsr
Creator
Creator
Author

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

sunny_talwar

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

aniketsr
Creator
Creator
Author

Hi Team,

Attached is the Sample Application to implement rolling 12

gwassenaarScriptingQlikView App Development