topic Problem when using date in QlikView

Problem when using date

Mon, 27 Oct 2014 11:04:11 GMT

Hi ,

I have a problem when using date.
I explain. I have two fields (DATE_VALIDATION and CALENDAR.DATE) which contain date values in the format 'YYYY-MM-DD'

I want to create a field which contains values from DATE_VALIDATION for the last 6 months from a certain date selected in CALENDAR.DATE.

For that I create two variables : last6month = addmonths(monthend(max(CALENDAR.DATE))+1,-6) and maxDate= max(CALENDAR.DATE).

I then created a selection which has the following expression : if(DATE_VALIDATION >= $(last6month) and DATE_VALIDATION <= $(maxDate),DATE_VALIDATION)

But the selection list shows me no values.

Someone can explain it ?

Re: Problem when using date

PrashantSangle — Mon, 27 Oct 2014 11:25:10 GMT

Hi,

When you use max()

that time your date field is converted to num format

therefore You have convert your Date_validation field in Num format then compare.

Do like this

if(DATE_VALIDATION >= $(last6month) and num(DATE_VALIDATION) <= $(maxDate),DATE_VALIDATION)

Regards

Re: Problem when using date

Mon, 27 Oct 2014 11:30:35 GMT

Still doesn't work

Re: Problem when using date

ali_hijazi — Mon, 27 Oct 2014 13:34:19 GMT

make all your date fields numbers

num(dayStart(your_date_field))

use whatever you want then on the chart use the number tab to make the dates readable

Re: Problem when using date

Tue, 28 Oct 2014 07:15:16 GMT

I think I found the problem. I don't know why but it comes from the max function

When I remove max in both variables it works!