topic "Between" dates using set analysis in QlikView

"Between" dates using set analysis

Wed, 02 Sep 2009 01:53:19 GMT

Hi,

I'm old to QV but new to set analysis and was wondering how to do something similar to "variable between date1 and date2" but I can't seem to figure it out. Anybody wanna help me out?

"Between" dates using set analysis

Wed, 02 Sep 2009 02:48:14 GMT

You can use multiple conditions in your Set Modifier. Dates always seem to take a couple of tries for me.

It should be something like:

Sum({<Field = {'>1/1/2009<1/31/2009'}>} Amount)

"Between" dates using set analysis

Wed, 02 Sep 2009 04:10:23 GMT

Thanks for your response, but for some reason I can't get this to work.

I'm trying to duplicate this:


sum( if(InvoiceDate >= makedate(2008,6,6) and InvoiceDate <= makedate(2008,8,8), Sales))

which works, with this:



sum({<InvoiceDate= {'>=6/6/2008<=8/8/2008'} >} Sales)



sum({<InvoiceDate= {'>=$(=makedate(2008,6,6)) <= $(=makedate(2008,8,8))}' >} Sales)

but it doesn't work..what can be wrong?

"Between" dates using set analysis

Oleg_Troyansky — Wed, 02 Sep 2009 04:21:31 GMT

I noticed that you are using single quotes... You need to use double quotes to signify "search" (as opposed to a simple list of values):

sum({<InvoiceDate= {">=6/6/2008<=8/8/2008"} >} Sales)

cheers!

Oleg

"Between" dates using set analysis

Wed, 02 Sep 2009 04:58:30 GMT

Thanks but still not working. This is driving me nuts. It's like QV doesn't even try to evaluate the expression (no progress bar on the chart after apply). Any pointers as to what can be wrong?

"Between" dates using set analysis

Wed, 02 Sep 2009 05:24:25 GMT

I just figured out what's wrong: I have to specify the date with the month name instead of the number, for example:


{">=06/Jun/2008<=06/Aug/2008"}

anyway to turn off this insanity and use numbers for the months?

"Between" dates using set analysis

johnw — Thu, 03 Sep 2009 01:18:44 GMT

The date format needs to be the same date format as you're using for your date field. So it looks like your real date field is formatted that way, which is to say 'DD/MMM/YYYY'. To use the number for the month, use 'DD/MM/YYYY' instead.

"Between" dates using set analysis

Thu, 03 Sep 2009 02:20:35 GMT

Hi John,

Is there anywhere else to specify the date format apart from the script? Cause in the script I have

SET

DateFormat='DD/MM/YYYY'

but I also have

SET

MonthNames

declared in the script. Could that be causing this mess?

"Between" dates using set analysis

Thu, 03 Sep 2009 02:26:14 GMT

I have these two in my Scripts:

SET DateFormat='M/D/YYYY';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

MM is usually 01-12, but maybe it could Jan-Dec? It may be worth it to try:

SET DateFormat='D/M/YYYY';

"Between" dates using set analysis

johnw — Thu, 03 Sep 2009 05:38:12 GMT

The month names shouldn't be causing a problem unless you tell QlikView to USE the month names. Format DD/MM/YYYY doesn't use month names. It uses month numbers.

Yes, there are a ton of places to set the date format. There's the variable that is normally set at the top of the script. You can override that when loading specific fields, such as date(MyDate,'MMMM, DD YYY') as MyLongDate. You can override it in the document properties for the field and setting "survive reload". You can override it on a chart by going to the Number tab and giving it a format for the date. There are probably other spots as well.

I have typically been explicit in my loading script for each date field which format I want. I can't think of any convincing reason why, though. I guess I just want to see it - here is my script, here is my format explicitly spelled out. I don't override at the document level, and I only rarely override at the chart level, so that's usually the end of the story in my applications.

In any case, here is a working example for you with some very simple data. Hopefully it will be helpful.

"Between" dates using set analysis

Thu, 03 Sep 2009 10:52:24 GMT

one thing I don't understand is why makedate() doesn't default to that format too

"Between" dates using set analysis

johnw — Fri, 04 Sep 2009 01:22:25 GMT

It's working for me. I'm doing a sample script with a makedate() in it, changing the SET DateFormat, and on a reload, the format of the date in a chart changes to match the new SET. But like I said, there are a LOT of places that the date format can get overridden, so your application is likely overriding it somewhere.

First place I'd check is the chart, but I'm guessing that isn't it.

Second place I'd check is settings -> document properties -> number -> your field. Does it have a date format? Is "Survive Reload" checked? Hmmm. Interesting, in my example, it DID have a date format, and survive reload WAS checked. I removed it, and put it back, and now it IS functioning as an override, where it wasn't before. Anyway, remove the checkmark on "Survive Reload" if it's there to stop overriding the default, or just set the date format you want to override the default.

"Between" dates using set analysis

Tue, 13 Oct 2009 09:51:17 GMT

I just ran into this problem too. Changing the number properties of the field in Document Settings was the fix. I also received the suggestion:

The best way is to use the integer value of the string as in Date#('01/01/2009', 'mm/dd/yyyy')

"Between" dates using set analysis

Tue, 13 Oct 2009 13:46:25 GMT

Hi,

i have a german Qlikview, can you hardcopie, where do you changed in Document Settings.

Thanks

Re: "Between" dates using set analysis

Wed, 10 Jul 2013 18:44:41 GMT

Hello John, i know this post is old but i think you can help me.

Just as Juan Fibonacci, i want to get the sum of all the sales made between two dates.

this is what i got:

=sum({<Date ={">=01/11/2012 <=01/02/2013"}>} Value)

The result is just the value that has this date value: '01/02/2013' if i take out the '=' it returns 0. For what i understood, it isn't looking for the other values, or something like that.

it worked this way:

=sum({<Date ={">=01/11/2012 <=01/02/2013"},month=,year=>} Value)

it gave me the sum, but there are two details here, first that in my proyect i am not using dates like this "01/11/2012" but as a field. and second that if i use the month=,year= those fields wouldn't work....

I am out of ideas do you have any? thank you.