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: 
alanmcgrath
Creator
Creator

RangeSum 2 Fiscal Years seperately in one chart

Hi All,

I'm trying to make a chart that will show a Monthly number as well as the Cumulative number.  I am able to get the cumulative number using the RangeSum function, however, what I really want is to RangeSum 2 Fiscal Years in the same chart. So I want to be able to show the Monthly value for FY17 & Fy18 as well as the Cumulative for FY17 & FY18.  The RangeSum as I have it is accumulating each month over the 2 years but I want it to restart the calculation at the new Fiscal year.

So I would like something like this:  Fiscal Year starts in September

DateMonthlyCumulative
5/31/2017100100
6/30/2017100200
7/31/2017150350
8/31/2017100450
9/30/2017100100
10/31/2017150250
11/30/2017200450
12/31/2017150600

Is there a way for me to do this?  Attached is a simple QVW with dummy data to illustrate the problem.

1 Solution

Accepted Solutions
sunny_talwar

Script

LOAD *,

YearStart(Date, 0, 9) as Year;

Load * Inline [

Date, Forecast

9/30/2016, 100

10/31/2016, 200

11/30/2016, 150

12/31/2016, 100

1/31/2017, 200

2/28/2017, 300

3/31/2017, 300

4/30/2017, 250

5/31/2017, 400

6/30/2017, 400

7/31/2017, 350

8/31/2017, 450

9/30/2017, 200

10/31/2017, 300

11/30/2017, 250

12/31/2017, 150

1/31/2018, 250

2/28/2018, 300

3/31/2018, 400

4/30/2018, 300

5/31/2018, 350

6/30/2018, 400

7/31/2018, 400

8/31/2018, 450

];

Expression

Aggr(RangeSum(Above(Sum(Forecast),0,RowNo())), Year, Date)

Capture.PNG

View solution in original post

2 Replies
sunny_talwar

Script

LOAD *,

YearStart(Date, 0, 9) as Year;

Load * Inline [

Date, Forecast

9/30/2016, 100

10/31/2016, 200

11/30/2016, 150

12/31/2016, 100

1/31/2017, 200

2/28/2017, 300

3/31/2017, 300

4/30/2017, 250

5/31/2017, 400

6/30/2017, 400

7/31/2017, 350

8/31/2017, 450

9/30/2017, 200

10/31/2017, 300

11/30/2017, 250

12/31/2017, 150

1/31/2018, 250

2/28/2018, 300

3/31/2018, 400

4/30/2018, 300

5/31/2018, 350

6/30/2018, 400

7/31/2018, 400

8/31/2018, 450

];

Expression

Aggr(RangeSum(Above(Sum(Forecast),0,RowNo())), Year, Date)

Capture.PNG

alanmcgrath
Creator
Creator
Author

Worked perfectly!  Thanks!