Hi,
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],
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:
if( DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')<=2011/09/24>=2011/08/28, 'September',
if( DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')<=2011/10/29>=2011/09/25, October,,
etc.
etc.
)))) as [Effective Trading Month]
Does this make any sense?
Thanks,
Gerhard
