Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
asmithbi
Contributor II

RangeSum of Previous Three Months

Hello,

I am trying to create a previous three month aggregation of Charges per row.  The issue is when I filter on the current year of 2016, January is not aggregating the previous months of November 2015,December 2015 and January 2016. I am using the RangeSum function with the Above function with the {1} to ignore the filter selection but, the beginning periods are not aggregating correctly.  The goal is to have a previous three month aggregation per row on the straight table regardless of which year I filter on except for the first year where data for the previous months do not exist.

Attached is the app with the current version of the previous three month expression. 

Any help would be very much appreciated. 

Thank you!

1 Solution

Accepted Solutions
girirajsinh
Contributor III

Re: RangeSum of Previous Three Months

Hi Alec,

I feel that you should use date related function instead of inter-record functions for table. In this case Above().

But anyway, I guess changing your expression as below would get you the desired result. I hope that would help you.

If(Sum(GrossCharges)=0,0, RangeSum( Above( Total Sum({1} GrossCharges ), 0, 3)))

Instead of

If(Sum(GrossCharges)=0,0, RangeSum( Above( Sum({1} GrossCharges ), 0, 3)))

Now result is like,

Cheers,

Giri

4 Replies
girirajsinh
Contributor III

Re: RangeSum of Previous Three Months

Hi Alec,

I feel that you should use date related function instead of inter-record functions for table. In this case Above().

But anyway, I guess changing your expression as below would get you the desired result. I hope that would help you.

If(Sum(GrossCharges)=0,0, RangeSum( Above( Total Sum({1} GrossCharges ), 0, 3)))

Instead of

If(Sum(GrossCharges)=0,0, RangeSum( Above( Sum({1} GrossCharges ), 0, 3)))

Now result is like,

Cheers,

Giri

Re: RangeSum of Previous Three Months

You can also look at this

RangeSum(Above(TOTAL Sum({<Year, ServiceMonth2, Month>} GrossCharges), 0, 3)) * Avg(1)

Note:

1) I am avoiding to use the if statement and multiply by Avg(1) which essentially does the same thing

2) I am not ignoring selections in all field using 1, because you may want charges to change based on your other dimension. For example if you have multiple employees, you may want the chart to show employee wise 3 month accumulation. If you use {1}, your expression won't filter based on any selection including employee. Instead of using {1}, I suggest ignoring only those selections such as Year and ServiceMonth2 where you plan to make a selection.

Capture.PNG

asmithbi
Contributor II

Re: RangeSum of Previous Three Months

Hi Giri,

That worked!  I appreciate the help. 

Take care,

asmithbi
Contributor II

Re: RangeSum of Previous Three Months

Hi Sunny,

I like your approach.  I see where I can use it in other situations. 

Thanks again! 

Community Browser