I currently use different extracts to do reports. One of our clients requested that I report using their trading calendars, where currently I report on normal calendar months.
Here is part of my script that concerns dates:
DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')) as [Effective Date], weekday(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY'))) as [Effective Day], month(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY'))) as [Effective Month], year(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY'))) as [Effective Year],
gg
This is taken from a Transaction Extract. It only has the field Effective Date, so I then create Effective Day, Effective Month and Effective Year, all of which I then use in expressions. I will for instance do analysis on Turnover for a particular month by looking at where the Effective Month is September.
Their trading calendar month is different, it runs (for this month) from 28/08/2011 to 24/09/2011. So when I do a certain report for September, it must not run from 1 to 30 September but from 28 August to 24 September.
What I would like to do is create a new field called Effective Trading Month. Please can you help me with the expression. It must be something like this but I can't get it right:
