Day Month

[upaliwije]

I have created a field (RISK_DAY_MONTH) in my master calendar. But when I applied same in following Expression it does not work

sum({<POL_TYPE={'N'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY_MONTH={'>=15-Aug <=13-Sep'}>}Nos)

Without that part expression works. Pls tell me what is wrong with my expression

[rubenmarin]

Hi Upali, date formats are always tricky, sometimes I create a date in number format just to apply filters without caring about formats.

First of all confirm that RISK_DAY_MONTH is a date and not a string (just add in a list box, by default dates are right-aligned and strings are left-aligned), if RISK_DAY_MONTH doesn't have a year I think it will be a string, so comparison with '<=' or '>=' won't work.

[upaliwije]

Is This type of Calculation Possible ?

[upaliwije]

As you said my RISK_DATE_MONTH is left aligned. But when I modify the script

date(RISK_DATE,'DD-MMM')AS RISK_DAY_MONTH) It is right aligned. Can I now apply comparison or what would you suggest me to do

[rubenmarin]

I feel more comfortable using numbers as filters, ie:

Num(date(RISK_DATE)) AS NumDate

The expression can be something like:

sum({<POL_TYPE={'N'},NumDate={">=$(=Num(Date('15/08/$(=Max(RISK_YEAR)-1)')))<=$(=Num(Date('13/09/$(=Max(RISK_YEAR)-1)')))"}>}Nos)

Note that you can see dates as DD-MMM, but internally it's also storing the year, so it's a complete date and you need the year to filter them.

Also, in my example I assumed DD/MM/YYYY as general date format, maybe yours is different.