topic RangeSum 2 Fiscal Years seperately in one chart in QlikView

RangeSum 2 Fiscal Years seperately in one chart

alanmcgrath — Tue, 31 Oct 2017 14:35:50 GMT

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.

Re: RangeSum 2 Fiscal Years seperately in one chart

sunny_talwar — Tue, 31 Oct 2017 14:43:37 GMT

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)

Re: RangeSum 2 Fiscal Years seperately in one chart

alanmcgrath — Tue, 31 Oct 2017 14:48:06 GMT

Worked perfectly! Thanks!