Always use first month selected as base period

[#1]

I have a table that has the fields Month-Year and Price as shown in the first two columns below.

I have to chart the percentage change from the users selected range of months. So say the users has selected Jan to May. I want January to always be the base period to calculate subsequent months percentage increase or decrease (as shown in column 3).  Now, if the user selects Feb to May, I want February to always be the based period to calculate subsequent months percentage increase or decrease (as shown in column 4).

This calculation needs to be an expression in a charl. The dimension is Month-Year.

How would I do this?

Thanks,
Stephen

Month-Year	Price	(User Selection is Jan to May) Percentage Change	(User Selection is Feb to May) Percentage Change
Jan-13	2	0
Feb-13	2.05	2%	0
Mar-13	1.9	-5%	-7%
Apr-13	2.08	4%	1%
May-13	2.1	5%	2%

[#2]

I think you could create two expressions, one that calculates the price for each MonthYear and the second that will calculate the percentage increase using above or below for the first expression.

[#3]

To add complexity to the problem, the table is 4 years of monthly pricing. The user could actually select Jan 2010, Jan 2011, Jan 2012 and Jan 2013 and the same calcualtion needs to work as in the chart above with Jan 2010 as the base period.

Stephen

[swuehl]

Try these two expressions:

=sum({<[Month-Year]>}Price)

=sum(Price)/aggr(top(sum(Price)),[Month-Year])  -1

Hope this helps,

Stefan

[#5]

Thanks Stefan. I will look at your example in more detail on Monday, but first glance seems to be what I want.

Stephen

[#6]

Didn't need the Aggr part of the expression. The Top expression returns the first row in a straight table and as that is how my sort is it works fine.

Stephen