RangeSum 2 Fiscal Years seperately in one chart

[alanmcgrath]

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

Date	Monthly	Cumulative
5/31/2017	100	100
6/30/2017	100	200
7/31/2017	150	350
8/31/2017	100	450
9/30/2017	100	100
10/31/2017	150	250
11/30/2017	200	450
12/31/2017	150	600

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

[sunny_talwar · Accepted solution]

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)

[alanmcgrath]

Worked perfectly!  Thanks!