Previous Month column - Increase between 2 periods - diferential calculation -

[#1]

Hello, i am having an issue with the following synthax:

=sum( {$<Date={$(AddMonths(Date,-1))}>} Quantity)

"Date" here is expressed in Months on a current 5 year period.

My goal is to build a graph and/or table on which for each 'Date' & Product values, it shows the Previous sum of quantity.

The ultimate goal is to calculate a monthly ratio showing the monthly increase with a formula such as (CurrentMonth - PreviousMonth)/CurrentMonth.

My current trials are not working.

What did I wrong or forgot to look at?

Thanks for your help

[#2]

Sum

({1<Date={$(=Max(date)-1)}>} Quantity)

[#3]

Hello Paco, thank you for your reply.

I i tried it too and it is not working.

By the way, what means the part "(Max(date)-1) ? here i want to select the previous month

Thanks for your help,

[johnw]

A set is evaluated once for the whole table, not for each row of the table. There's a complicated way around that restriction, but it certainly isn't how I'd solve this particular case. Instead, I'd generate an AsOf table:

AsOf:
AsOfMonth, MonthType, Month
Jan 2011, Current, Jan 2011
Jan 2011, Previous, Dec 2010
Dec 2010, Current, Dec 2010
Dec 2010, Previous, Nov 2010
etc.

Dimension = AsOfMonth
Expression = 1 - sum({<MonthType={'Current'}>} Quantity)/sum({<MonthType={'Previous'}>} Quantity)

[#5]

Hello, i finally created 2 additional data called Year and Month and applied the following expressions:

sum

({<Date={">=$(=date(MonthStart(MakeDate(Max(Year),Max(Month)),-Duration+1))) <=$(=addmonths(MakeDate(Max(Year),Max(Month)),0))"

},

Year

=,

Month=

>}

Quantity)

This allows me to calculate the sum of the time window i wanted to get.

Thanks for your help.

Bruno