topic Comparing dates in Set Analysis in QlikView

Comparing dates in Set Analysis

vlad_komarov — Tue, 17 Jan 2012 15:32:53 GMT

Hello,

I have two dates in my table: [Current Service Date] and [Initial Service Date]. I need to display totals based on these dates comparison:

I thought this should work:

Sum ({< [Current Service Date] = {'>=([Initial Service Date])'} >} [Quantity])

But it does not.

Both Date fields above are numeric. Any suggestions what I am missing here?

Best regards,

Vladimir

Comparing dates in Set Analysis

swuehl — Tue, 17 Jan 2012 15:45:01 GMT

Vladimir,

do you have field with unique values like created using recno() ?

Then try this

sum( {< [RecID] = {"=[Current Service Date]>=[Initial Service Date]"} >} [Quantity] )

Note that I used a search expression using double quotes, not single quotes.

Hope this helps,

Stefan

Comparing dates in Set Analysis

erichshiino — Tue, 17 Jan 2012 19:30:28 GMT

HI,

Vladimir

You can also create a flag on script for this task:

Load [Current Service Date], [Initial Service Date] ,

...

if( [Current Service Date] >=[Initial Service Date] , 1, 0 ) as DateFlag

...

From...

Then, on your chart, the set analysis expression will be:

Sum ({<DateFlag={1} >} [Quantity])

Hope this helps,

Erich

Comparing dates in Set Analysis

vlad_komarov — Tue, 17 Jan 2012 21:11:26 GMT

Thanks Stefan,

Your suggestion works better than mine (:-)), but I've noticed that I cannot use '<=' check there..Is it because its similarity with Set Analysis syntax? I am going to check my data as well since it might contribute to the latest problem.

But thanks for a hint to the right direction.

Regards,

Vladimir

Comparing dates in Set Analysis

swuehl — Tue, 17 Jan 2012 21:58:42 GMT

Vladimir,

are you saying, that

=sum( {< [RecID] = {"=[Current Service Date]>=[Initial Service Date]"} >} [Quantity] )

does work ok, but

=sum( {< [RecID] = {"=[Current Service Date]<=[Initial Service Date]"} >} [Quantity] )

does not?