topic Re: Range sum with above function in QlikView

Range sum with above function

clarenceclay — Wed, 22 Nov 2017 09:56:49 GMT

Hi folks,

Refer above line chart. Im using range sum with above function. How can we use range sum with above function but limit the years to show only top 5 years say from 2012 to 2017. 2012 sum would be the total from the start year up to 2012.

Re: Range sum with above function

sunny_talwar — Wed, 22 Nov 2017 16:51:22 GMT

May be multiply you expression with this

Avg({<Year = {"$(='>=' & (Max(Year) - 5) & '<=' & Max(Year))"}>} 1)

Re: Range sum with above function

clarenceclay — Thu, 23 Nov 2017 01:12:11 GMT

Hi Sunny,

Really appreciate your answer but I am not so sure how am i going to incorporate your solution into my current expression below

Expression

Rangesum(above(count(Company_no),0,RowNo()))

Thank you in advance

Re: Range sum with above function

sunny_talwar — Thu, 23 Nov 2017 23:04:55 GMT

Just this

RangeSum(Above(Count({<Year>}Company_no),0,RowNo())) * Avg({<Year = {"$(='>=' & (Max(Year) - 5) & '<=' & Max(Year))"}>} 1)

Re: Range sum with above function

clarenceclay — Fri, 24 Nov 2017 03:25:28 GMT

Hi Sunny.

Your solution works great. Love it. One last question, how can we get the chart to only display those 2012 and above data? get rid of those empty year values.

Again, thank you very much for your professional help.

Re: Range sum with above function

clarenceclay — Fri, 24 Nov 2017 07:09:29 GMT

Hi Sunny, I figured it out. Just unchecked the include zero values. Thanks again for your lovely solution.

Re: Range sum with above function

sunny_talwar — Fri, 24 Nov 2017 13:07:19 GMT

Superb

Re: Range sum with above function

sunny_talwar — Fri, 24 Nov 2017 13:07:42 GMT

Please close the thread by marking the correct response.

Best,

Sunny