topic Re: Comparing two dates in Set Analysis in QlikView

Comparing two dates in Set Analysis

Mon, 10 Sep 2012 17:51:00 GMT

Hi, I am very new to Qlikview and I am comparing two different dates. In my script I set

LET vThreeDayAgo = Dayname(today()-3); so the variable ends up looking like this 09/07/2012 today.

I am comparing this in this set analysis syntax but get the sum of everything for DaysFilled.Days in the table.

The DaysFilled.Date is formatted mm/dd/yyyy. I have verified that there is a DayFilled.Date = 09/07/2012.

sum({$<DaysFilled.Date={"=vThreeDayAgo"}>} DaysFilled.Days)

Can someone tell me what is wrong with the above Set analysis?

Any ideas would be greatly appreciated.

Thank you,

OnePuttGirl

Re: Comparing two dates in Set Analysis

whiteline — Mon, 10 Sep 2012 17:56:52 GMT

sum({$<DaysFilled.Date={"=DaysFilled.Date=$(vThreeDayAgo)"}>} DaysFilled.Days)

Re: Comparing two dates in Set Analysis

MayilVahanan — Mon, 10 Sep 2012 17:59:35 GMT

sum({$<DaysFilled.Date={$(vThreeDayAgo)}>} DaysFilled.Days)

Edit:

sum({$<DaysFilled.Date={'$(vThreeDayAgo)'}>} DaysFilled.Days) miss qoutes..

hope it helps

Re: Comparing two dates in Set Analysis

Mon, 10 Sep 2012 18:09:55 GMT

Hi,

Thank you for your response. I don't know why but when I do this I get null values. And the one above this I get zeros.

Any ideas why?

Re: Comparing two dates in Set Analysis

Miguel_Angel_Baeyens — Mon, 10 Sep 2012 18:18:17 GMT

Hi,

Probably it's due to the fact that Dates are represented as literals (strings) so using single quotes should work:

Sum({< DaysFilled.Date = {'$(vThreeDayAgo)'} >} DaysFilled.Days)

or event to the fact that you need to expand the variable using the equal sign and using single quotes:

Sum({< DaysFilled.Date = {'$(=vThreeDayAgo)'} >} DaysFilled.Days)

Hope that helps.

Miguel

Re: Comparing two dates in Set Analysis

Mon, 10 Sep 2012 18:22:53 GMT

Miguel,

I did both of the expressions and I still get zeros. Is there anything else you would try?

Re: Comparing two dates in Set Analysis

whiteline — Mon, 10 Sep 2012 18:35:20 GMT

As my and Miguel's expressions gives you zeros,

I suggest create a table box and check to be sure that zeros is not the right answer.

Re: Comparing two dates in Set Analysis

Mon, 10 Sep 2012 18:40:42 GMT

How do I attach my .qvw?

Re: Comparing two dates in Set Analysis

Mon, 10 Sep 2012 18:43:16 GMT

Ok, I attached a qvw and the expression is for Monday. Can you tell me why I'm getting nothing?

Re: Comparing two dates in Set Analysis

Mon, 10 Sep 2012 18:47:20 GMT

Zeros is not the correct answer. Can you check my .qvw I attached and see if you can get the correct result?

Re: Comparing two dates in Set Analysis

whiteline — Tue, 11 Sep 2012 05:06:15 GMT

Use this (as your vThreeDayAgo is just a string):

=Sum({< DaysFilled.Date = {'=$(vThreeDayAgo)'} >} DaysFilled.Days)

Re: Comparing two dates in Set Analysis

Tue, 11 Sep 2012 06:55:51 GMT

try this exp:

sum({<Date = {">=$(#=vStartdate) <$(#=vEndDate)"}>} value)

Hopefully, this will solve your problem.

If this exp doesn't work then remove '=' sign and try again:

sum({<Date = {">=$(#vStartdate) <$(#vEndDate)"}>} value)

Re: Comparing two dates in Set Analysis

Tue, 11 Sep 2012 06:56:13 GMT

try this exp:

sum({<Date = {">=$(#=vStartdate) <$(#=vEndDate)"}>} value)

Hopefully, this will solve your problem.

If this exp doesn't work then remove '=' sign and try again:

sum({<Date = {">=$(#vStartdate) <$(#vEndDate)"}>} value)

Re: Comparing two dates in Set Analysis

Miguel_Angel_Baeyens — Tue, 11 Sep 2012 18:33:51 GMT

Hi,

The format in the variable and the field are not the same, therefore the zeroes in the result. Check the ammended application and see how the variable has changed and works.

Hope that helps.

Miguel

Re: Comparing two dates in Set Analysis

Tue, 11 Sep 2012 19:34:18 GMT

Thank you, Thank you, Thank you!
Miguel I did finally get that they were different formats. Thank you for the followup because it's really nice to know there are people out there to help with problems.

Re: Comparing two dates in Set Analysis

rajni_batra — Wed, 12 Sep 2012 08:23:20 GMT

Hi miguel,

i saw application of both but could nt get any change in the variable its dayname(today()-3) in both but output of variables are diffrent in both.. how?

Can u plz explain a bit