topic Re: Set analysis Date Range issue in QlikView

Set analysis Date Range issue

Fri, 21 Mar 2014 14:52:57 GMT

Hi,

My expressions

count({<CREATED_DT= {"=>$(vSt_Date)"}>}DISTINCT(S_CODE)) as well as

count({<CREATED_DT= {">=$(vSt_Date)"}>}DISTINCT(S_CODE))

gives 0, but expression

count({<CREATED_DT= {"=$(vSt_Date)"}>}DISTINCT(S_CODE))

gives the count matching to CREATED_DT

What could be the issue in my first 2 expressions where in I want to get the count >= the date.

Thanks

Lax

Re: Set analysis Date Range issue

alexandros17 — Fri, 21 Mar 2014 14:56:12 GMT

The right one is >=, however try writing

count({<CREATED_DT= {">=01/01/2014"}>}DISTINCT(S_CODE)) and verify the result (obviously set your date), I think that the problem is date format of the variable

Re: Set analysis Date Range issue

Fri, 21 Mar 2014 15:07:22 GMT

Hi,

Had it been date format issue then my second expression should have also failed.'

count({<CREATED_DT= {"=$(vSt_Date)"}>}DISTINCT(S_CODE)) gives the proper count matching the date.

CREATED_DT is in DD-MM-YYYY and vSt_Date is a variable with value as 06-03-2014

Re: Set analysis Date Range issue

PrashantSangle — Fri, 21 Mar 2014 15:10:44 GMT

Hi,

You made a very small mistake

Count({<CREATED_DT= {"=>$(vSt_Date)"}>}DISTINCT(S_CODE))

instead of this

You have to write

Count({<CREATED_DT= {">=$(=vSt_Date)"}>}DISTINCT(S_CODE))

Regards,

Re: Set analysis Date Range issue

alexandros17 — Fri, 21 Mar 2014 15:14:32 GMT

The question is that syntax is correct, I should use ' instead of " but everything is ok

Please try

count({<CREATED_DT= {'=$(vSt_Date)'}>}DISTINCT S_CODE)

count({<CREATED_DT= {'=$(=vSt_Date)'}>}DISTINCT S_CODE)

Re: Set analysis Date Range issue

Fri, 21 Mar 2014 15:16:48 GMT

Hi,

Just to verify the date format issue...I have to get the following code to equate the dates.

if(Date#(CREATED_DT,'DD-MM-YYYY') >= Date#('06-03-2014','DD-MM-YYYY'),1,0)

above code works when dates are formatted. How do I handle this in the set expressions ??

Re: Set analysis Date Range issue

Fri, 21 Mar 2014 15:17:46 GMT

try below

count({<CREATED_DT= {"=>$(=Date(vSt_Date,'MM/DD/YYYY'))"}>}DISTINCT(S_CODE))

Re: Set analysis Date Range issue

SunilChauhan — Fri, 21 Mar 2014 15:18:07 GMT

make sure your CREATED_DT should be Number format

Num(CREATED_DT) as CREATED_DT_num

calculate max on the basis of CREATED_DT_num

then use your expression same

hope this helps

Re: Set analysis Date Range issue

PrashantSangle — Fri, 21 Mar 2014 15:20:31 GMT

Hi,

Actually there is no need to convert but if you want you can convert at script level

as following

Date#(CREATED_DT,'DD-MM-YYYY') as CREATED_DT

Regards,

Re: Set analysis Date Range issue

Fri, 21 Mar 2014 15:27:48 GMT

This worked

count({<CREATED_DT= {">=$(=Date#(vSt_Date,'DD-MM-YYYY'))"}>}DISTINCT(S_CODE))

Thanks

Lax