"Between" dates using set analysis

[#1]

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?

[#2]

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)

[#3]

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)

or

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

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

[Oleg_Troyansky]

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

[#5]

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?

[#6]

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?

[johnw]

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.

[#8]

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?

[#9]

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';

[johnw]

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.