Selection of fields between 2 dates

[Dayna]

Hello,

I am hoping that someone here could help me please?
No doubt this will be an easy one... How do I show a selection of fields between two dates?

I was thinking set analysis, create 2 data islands of my date field, and then something like:

({<[Due Date] = {"<=$([Due Date1])>=$([Due Date2])"}>} [Qty Ord])

But unsure on how to do this without using sum... I just want to show the fields between the date range.

You're help would be appreciated!

Kind Regards.
Dayna

[erichshiino]

Hi Dayna,

I did something like this a few days ago using macros.

When the user selects a range in a field (data), I select the highest possible date on a secondfield (ClusterDate)

I used: ActiveDocument.GetField(Field).Select

you may change the syntax according to your need. See examples from the APIGuide below

you can also see my example attached

--------------------------------------------------

ActiveDocument.Fields("Month").Select "September"

ActiveDocument.Fields("Month").Select "J*"

ActiveDocument.Fields("Sales").Select ">1500"
--------------------------------------------------

Erich

[#3]

Hi,

please have a look at the attached example

Note:

• I have added two triggers for the OnChange-event of the two variables
• In both cases the following code will be executed:

sub filterDateRange
Dim vDate1
Dim vDate2
Dim intDate1
dim intDate2
dim selection

vDate1 = ActiveDocument.Variables("vDateSelection1").GetContent.String
vDate2 = ActiveDocument.Variables("vDateSelection2").GetContent.String
intDate1 = ActiveDocument.Evaluate("=num('$(vDateSelection1)')")
intDate2 = ActiveDocument.Evaluate("=num('$(vDateSelection2)')")
'check if the second date is before the first date
if (intDate2 < intDate1) then
 msgbox("Date 2 must not be before Date 1")
 ActiveDocument.Variables("vDateSelection2").SetContent "" & vDate1 & "", false
 exit sub

end if

selection = ">" & vDate1 & "<" & vDate2
ActiveDocument.Fields("DateField").Select selection
end sub

Hope this helps ...

Best regards

Stefan

[#4]

In addition to my previously posted solution with macros you could also use a formula to display your desired fields:

=if(DateField > vDateSelection1 and DateField < vDateSelection2, DateField)

or for using an other field

=if(DateField > vDateSelection1 and DateField < vDateSelection2, OtherField)

Please have again a look at the updated example (attached file).

Best regards

Stefan

[#5]

sorry, adding the attachmet does not work at the moment (I receive a timeout error of the forum), so another try

[#6]

Sorry, upload of documents in this forum is broken:

Download the file at:

http://www.qlikblog.at/downloads/Demo_SelectingTwoValues.qvw

[Dayna]

Hello Stefan,

Thank you so much for the attached, it is exactly what I am after... Two small (daft) queries, what did you put in the variable and I am not that familiar with triggers yet, how do I put an on change trigger as you have done in the attached?

Kind Regards,
Dayna

[#8]

Dayna Litherland wrote:
Hello Stefan,
Thank you so much for the attached, it is exactly what I am after... Two small (daft) queries, what did you put in the variable and I am not that familiar with triggers yet, how do I put an on change trigger as you have done in the attached?
Kind Regards,
Dayna<div></div>

Are you using QlikView 8.5 or QlikView 9 ...?

Best regards

Stefan

[Dayna]

I am on QlikView 9 - my only other small issue at the moment is the Date Selection is selecting the date in a number format, have I missed something to change it back to a date format? Otherwise your example is perfect!

Kind Regards,
Dayna

[#10]

Dayna Litherland wrote:
I am on QlikView 9 - my only other small issue at the moment is the Date Selection is selecting the date in a number format, have I missed something to change it back to a date format? Otherwise your example is perfect!
Kind Regards,
Dayna<div></div>

Change the field to date in your load script: date(A)

then you can use the document standard for it; furthermore you could format the value in the calendar-box (but with some date-formats, depending on your region, there is a bug i qlikview 9 sr1 not showing the correct format, therefore I have only used the document standard).

Hope this helps ...

Best regards

Stefan