topic Re: Comparing date in set-analysis doesn't work in QlikView

Comparing date in set-analysis doesn't work

Tue, 27 Aug 2013 15:46:16 GMT

I have no clue why this work:

= if ([Unavailable Start Date] < $(vEndDate4), 'true', 'false')

And this also:

( {$< [WorkItem
Enddate] = {">=$(vBeginDate4)<=$(vEndDate4)"}
>} [WorkType Todo] )

But this doesn't:

count( {$< [Unavailable Start Date] = {">=$(vBeginDate4)<=$(vEndDate4)"} >} [Unavailable
Start Date] )

Re: Comparing date in set-analysis doesn't work

jagan — Tue, 27 Aug 2013 15:50:52 GMT

Hi,

try this

count( {$< [Unavailable Start Date] = {">=$(=vBeginDate4)<=$(=vEndDate4)"} >} [Unavailable Start Date] )

If not working check whether the date format in variables and dimension are same.

Regards,

Jagan.

Re: Comparing date in set-analysis doesn't work

Tue, 27 Aug 2013 16:07:17 GMT

It doesn't work.

How do I check? Because doesn't this depens on the formatting of listbox?

When i put no formatting on the listboxes their appearence is not the same.

[Unavailable Start Date] -> displays as a date

vBeginDate4 -> as a number

Re: Comparing date in set-analysis doesn't work

Oleg_Troyansky — Tue, 27 Aug 2013 18:51:55 GMT

Gerry - you got it! The problem is caused by the fact that the two values are formatted differently, and it's important in Set Analysis.

So, for example:

if ([Unavailable Start Date] < $(vEndDate4), 'true', 'false') works because QlikView evaluates the expression, using the numeric value of the date field, and comparing it to the numeric value of the variable.

count( {$< [Unavailable Start Date] = {">=$(vBeginDate4)<=$(vEndDate4)"} >} [Unavailable
Start Date] ) - doesn't work, because the field is formatted as a date, and the variables are formatted as numbers, and that's an incorrect syntax in Set Analysis.

I'd suggest one of the two following solutions:

1. Format the variables as Dates:

count( {$< [Unavailable Start Date] = {">='$(=date(vBeginDate4))'<='$(date(vEndDate4))'"} >} [Unavailable
Start Date] )

2. Re-work the condition into a Boolean condition that can compare the field with the variable numerically:

count( {$< [Unavailable Start Date] = {"=(num([Unavailable Start Date])>=$(vBeginDate4) and num([Unavailable Start Date])<=$(vEndDate4))"} >} [Unavailable Start Date] )

can't guarantee the syntax 100%, I only typed it here with no validation...

best,

Oleg Troyansky

www.masterssummit.com

www.naturalsynergies.com

Re: Comparing date in set-analysis doesn't work

jagan — Wed, 28 Aug 2013 03:10:45 GMT

Hi,

try this

=count( {$< [Unavailable Start Date] = {">=$(=Date(vBeginDate4))<=$(=Date(vEndDate4))"} >} [Unavailable Start Date] )

If not working check whether the date format in variables and dimension are same.

Regards,

Jagan.

Re: Comparing date in set-analysis doesn't work

Wed, 28 Aug 2013 09:26:30 GMT

Thanks Jagan and Oleg,

I used this expression:

=count( {$< [Unavailable Start Date] = {">=$(=Date(vBeginDate4))<=$(=Date(vEndDate4))"} >} [Unavailable Start Date] )

And it works fine.

Re: Comparing date in set-analysis doesn't work

jagan — Wed, 28 Aug 2013 10:57:12 GMT

Hi,

Please close this disucssion, so that it would be helpful for others to identify solution for similar tasks.

Regards,

Jagan.