Year Month Calculation

[Anonymous]

Hi Friends

Wish you all a very happy New Year 2012!

I have an issue.

In my script, I use the following functions to calculate 'YearMonth' and it returns the value as for example: Jan 2012 as '201201'.

Year([Posting Date]) as Year,

Month([Posting Date]) as Month,

Year([Posting Date]) & num(Month([Posting Date]) ,'00') as YearMonth,

In my expression, I am using a formula to calculate the transactions that were made on last month.  The expression i use is

=max(YearMonth) - 1

Till last month, I didnt face any issues because when I used this function and when the max Year Month was '201112', the expression was returning the value '201111'.  But this month, it is returning a value '201200' (201201 - 1 is 201200) and because of this my whole report is going for a toss.  Can anyone help me to get out of this situation?

Thanks!

Balaji Ramakrishnan

[swuehl · Accepted solution]

Try defining your YearMonth as

Date(monthstart([Posting Date]),'YYYYMM') as YearMonth,

instead, then use

=Date(addmonths(max(YearMonth),-1),'YYYYMM')

Hope this helps,

Stefan

[Anonymous]

Hi Stefan

Thanks a lot.  It worked.

Cheers!

Balaji Ramakrishnan