Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Thanks for your help Miguel it did work.
Appreciated
Louw