calculate past 4 months total income

[#1]

Hi,

Would appreciate some advice here.

I am trying to calculate the past 4 monts total income. I have a field period that is the yearmonth like 201102. I have tried the following but it is not working.

=

=Sum({$<Period={$(=Max(Period)-1)}>} (AmountExcl)) + Sum({$<Period={$(=Max(Period)-2)}>} (AmountExcl)) + Sum({$<Period={$(=Max(Period)-3)}>} (AmountExcl)) + Sum({$<Period={$(=Max(Period)-4)}>} (AmountExcl))

But what happen is it start correctly with the first yearmonth that is 201102 the with yearmonth 201101 then with yearmonth 201100 that is wrong - it is supposed to be 201012

and then with last month that is 201011 that is november

I have a datefield like 2011/01/01if i need to use a date field

Thank you

Louw

[Miguel_Angel_Baeyens]

Hello Louw,

You can use Date() function and AddMonths() to get the results you want for date values

=Date(AddMonths('2011/01/01', -1), 'YYYYMM')

Then use a variable instead of selecting in a field to let the user select the offset (if any) or do it manually in your formula

Sum({< Period = {'>=$(=Date(AddMonths(Max(Date#(Period, 'YYYYMM')), -4), 'YYYYMM'))'} >} AmountExcl)

Hope that helps.

[#3]

Thanks for your help Miguel it did work.

Appreciated

Louw