0 Replies Latest reply: Sep 28, 2011 6:46 AM by Gerhard Laubscher RSS

    Trading Calendar Month in Script

    Gerhard Laubscher



      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,,



      )))) as [Effective Trading Month]



      Does this make any sense?