Remove NULL values that are being counted

Fri, 11 Mar 2011 11:34:02 GMT

Hi Everyone,

I have the following formula:

=count( {$<R_SALE_START_DATE = {">=$(TY_YTD_Start)<=$(=date(REPORT_YTD, 'YYYY-MM-DD'))"}>} DISTINCT

IF(aggr(min(distinct R_SALE_START_DATE), R_CLEAN_CONTACT_WID)= R_SALE_START_DATE,

R_CLEAN_CONTACT_WID))

I'm getting an incorrect result because there are null values for min(R_SALE_START_DATE) which are being counted. So I tried something like this:

=count( {$<R_SALE_START_DATE = {">=$(TY_YTD_Start)<=$(=date(REPORT_YTD, 'YYYY-MM-DD'))"}>} DISTINCT

IF(aggr(min(distinct R_SALE_START_DATE), R_CLEAN_CONTACT_WID)= R_SALE_START_DATE,

IF(isnull(aggr(min(distinct R_SALE_START_DATE), R_CLEAN_CONTACT_WID)=0,

R_CLEAN_CONTACT_WID)))

However, QV still counts the null dates as being a match for R_SALE_START_DATE when it isn't. I.e it has not fixed the null problem.

Any ideas? Does AGGR treat Nulls differently or something?

Thanks

GPC

topic Remove NULL values that are being counted in QlikView