Issue with yeartodate() timestamp

[#1]

Hello,

I am trying to use the yeartodate() function in the following way:

=Count(if(yeartodate(R_SALE_START_DATE,0,1,REPORT_YTD),R_ACCOUNT_NUMBER))

REPORT_YTD is a distinct value (a date).

My issue is simple, this function should provide me exactly what I need but something is happening with the ref_timestamp (REPORT_YTD). If I remove this ref_timestamp and just let the function default to today's date I get the results I want, but if I include this variable which is a few days earlier than today's date, I get values twice the size.

As a final test, if I simply write the date (e.g. '2011-02-18') instead of using the field, then I get the correct values!!

One work around I can see (failing to get help on this issue) is to test the dates, so part 2 of my question is to evaluate dates (i.e. < = <=, >, ) can someone please shed some light on the correct evaluation syntax for date fields?

Thanks

GPC

[#2]

I am assuming that REPORT_YTD is a variable that you have manually entered. If so, QlikView doesn't know the data type. I would add the DATE function into your formula.

=Count(if(yeartodate(R_SALE_START_DATE,0,1,Date(REPORT_YTD,'YYYY-MM-DD')),R_ACCOUNT_NUMBER))

Thanks,

Aline

[#3]

Thanks for your suggestion.

If I do Date(REPORT_YTD) it gives me a result that is exactly double the true result. This seems extremely odd, has anyone had this happen before?

Thanks

[#4]

Sorry,

Make this more clear:

REPORT_YTD = '2011-02-18'

If I use the variable name as in my example I get a doubled answer (exactly twice what I am expecting).

If I simply enter '2011-02-18' into the yeartodate() formula as my timestamp I get the correct answer.

This is driving me crazy.

Thanks

GPC

[#5]

Try adding a DISTINCT into the formula:

Count( Distinct if(yeartodate(R_SALE_START_DATE,0,1,Date(REPORT_YTD)),R_ACCOUNT_NUMBER))