topic Re: Day Month in QlikView

Day Month

upaliwije — Sat, 16 Sep 2017 09:49:59 GMT

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

Re: Day Month

rubenmarin — Sat, 16 Sep 2017 11:50:20 GMT

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.

Re: Day Month

upaliwije — Sat, 16 Sep 2017 11:55:18 GMT

Is This type of Calculation Possible ?

Re: Day Month

upaliwije — Sat, 16 Sep 2017 12:01:46 GMT

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

Re: Day Month

rubenmarin — Sat, 16 Sep 2017 12:14:27 GMT

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.