Rolled Sum

[#1]

Hi All,

I have some data from Jan 2007 to Dec 2009. I need to display a chart/straight table where the dimension will be the month and expression will be the year 2008 and 2009.

For year 2008 Jan month, I need the sum of Feb 2007 to Jan 2008 and for feb month in 2008, I need the sum of Mar 2007 to Feb 2008 and so on.

I tried the below code but I am not getting the correct sum. Find the data below

sum({<Period=, Months={"$(='<='&Only(Months)&'>'&Only(addmonths(Months,-12)))"} >} Amount )

data

Months	Amount
Jan-07	7276
Feb-07	9071
Mar-07	7888
Apr-07	8583
May-07	7721
Jun-07	7876
Jul-07	7526
Aug-07	8049.1
Sep-07	7763
Oct-07	8064
Nov-07	9150
Dec-07	9144
Jan-08	11195
Feb-08	9858
Mar-08	10801
Apr-08	9895
May-08	10796
Jun-08	11864
Jul-08	8902
Aug-08	9130
Sep-08	9483
Oct-08	9020
Nov-08	9569
Dec-08	10792
Jan-09	4317
Feb-09	8504
Mar-09	8381
Apr-09	10101
May-09	9325
Jun-09	13515
Jul-09	8675
Aug-09	8803
Sep-09	7048
Oct-09	8691
Nov-09	9159
Dec-09	9673

Hope someone can help me

Best Regards,

Sajeevan

[#2]

You could try to run a sum for all year minus sum for the month N-1 ?

using set analysis ?

[#3]

Hi,

Can you share how the code will be?

Thanks,

Sajeevan

[Anonymous]

Hi Sajeevan,

First you need to make sure that the Months field is in a proper date format. I added a preceding load to your table in the script:

Data:
Load
date(date#(Months,'MMM-YY'),'MMM-YY') as Months,
Amount;
LOAD * INLINE [
Months, Amount
Jan-07, 7276
Feb-07, 9071
Mar-07, 7888
Apr-07, 8583

etc.

Then you should be able to use an expression like this:

sum({<Period=, Months={"$(='<='&Only(Months) & '>' & only(date(addmonths(Months,-12),'MMM-YY')))"} >} Amount )

Notice that I've added another date() around the addmonths() function. This is because the addmonths() function will format the date value as a full date with days included and we want to strip it down to MMM-YY.

[#5]

Hi Johannes,

Thanks for your response. I have the proper date format in Months field. I am not getting the desired result with this expression even after adding the date formating while loading and in the expression. Ideally the rolled sum should have been 102,030 for the month Jan in year 2008 (Feb-07 to Jan-08) but I am getting 22,788.

I think this expression will work only if I select a month otherwise it shows Months={"<=>"}. I am not providing a month selection for the user. If I select the month as Jan-08 then the expression shows Months={"<=Jan-08>Feb-07"} and the rolled sum is only for Jan-08

What I wanted in my chart is

- the dimention month(Months)

- expression for year 2008

- expression for year 2009

I don't know how to attach my QVW file in this mail. I have both versions 8.5 and 9.0