Date does not work in set analysis.

[baylor2016]

Hi,

The expression below used to get a count by specifying the date ranges DOES NOT work.

=count(distinct {$<A1bDate={">=$(=Date(vToday-365))<=$(=Date(vToday))"} >} PAT_ID)

It always returns ZERO. It looks like Qlikview does not recognize the date field - A1bDate.

select b.PAT_ENC_CSN_ID, a.ORD_NUM_VALUE as A1c, B.ORDERING_DATE as A1bDate , A.RESULT_DATE, b.PROC_ID, b.PROC_CODE,b.DESCRIPTION

from ORDER_PROC b    

I also tried to change the date format at LOAD step,  still no luck.

load *,

Date(ORDERING_DATE,'DD/MM/YYYY') as A1cDate;

This data field can be formatted as integer or date, but when it is used in set analysis, problem occurs.

I realized that when the date type of data was imported into Qlikview from Excel. This problem goes away.

Very strange problem.

Thanks

[swuehl]

It's hard to tell what's wrong with the information provided.

QV is case sensitiv regarding field names, so double check that all field names are spelled correctly and do exist in the model.

Create list boxes for the date fields and check the format. Is the format consistent and showing textual dates or numbers? Are textual dates right aligned, indicating an underlying numeric representation?

What is the date format of your A1bDate?

What is the value of vToday? What does

=Date(vToday)

show in a textbox? Only if this value format matches the format of your A1bDate field, the search may work correctly.

See also

Why don’t my dates work?

Get the Dates Right

Dates in Set Analysis

[baylor2016]

I know the field name is sensitive and I generally copy the field name instead of typing in. So field name is not problem.

I created a listbox and the date format is consistent. It shows as textual dates but it comes with time 12:00:00 AM. It might cause the problem since the variable I used does to have time.  The date in listbox is right aligned.

vToday is a variable defined as today()

Thanks

[swuehl · Accepted solution]

The format of your set modifier element(s) and the format of the field must be consistent. Both should be textual and both show date & time then. You can format your today() value just by using Timestamp() format function, or use Now() function to retrieve a real timestamp.

[#5]

Just a note.  Never use NOW() function directly in the expression as that could cause unrealistic delays in calculation due to the non deterministic nature of the function.  May be you can grab that in a variable and use it in the set expression.