Trading Calendar Month in Script

gerhardl — Wed, 28 Sep 2011 10:46:56 GMT

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.

)))) as [Effective Trading Month]

Does this make any sense?

Thanks,

Gerhard

topic Trading Calendar Month in Script in QlikView

Trading Calendar Month in Script