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

    Trading Calendar Month in Script

    Gerhard Laubscher

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

      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:

       

      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