topic Selection of fields between 2 dates in QlikView

Selection of fields between 2 dates

Dayna — Wed, 07 Oct 2009 23:16:46 GMT

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

Selection of fields between 2 dates

erichshiino — Thu, 08 Oct 2009 00:18:17 GMT

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

Selection of fields between 2 dates

Thu, 08 Oct 2009 03:20:04 GMT

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

Selection of fields between 2 dates

Thu, 08 Oct 2009 03:43:42 GMT

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

Selection of fields between 2 dates

Thu, 08 Oct 2009 03:45:43 GMT

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

Selection of fields between 2 dates

Thu, 08 Oct 2009 03:49:35 GMT

Sorry, upload of documents in this forum is broken:

Download the file at:

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

Selection of fields between 2 dates

Dayna — Thu, 08 Oct 2009 15:21:01 GMT

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

Selection of fields between 2 dates

Thu, 08 Oct 2009 15:29:40 GMT


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

Selection of fields between 2 dates

Dayna — Thu, 08 Oct 2009 18:16:10 GMT

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

Selection of fields between 2 dates

Thu, 08 Oct 2009 23:34:53 GMT


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

Selection of fields between 2 dates

Thu, 08 Oct 2009 23:37:25 GMT


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>

To set the triggers got to "Document Settings" and "Triggers" and choose the actions for the "variable event triggers" ... these (in my example) are calling a macro which you can find in the macro-editor (Ctrl + M)

Best regards

Stefan

Selection of fields between 2 dates

Dayna — Fri, 09 Oct 2009 18:51:24 GMT

Hello Stefan,

Many thanks for your quick reply, when I make a change to the source data on your example it stops working. Are you able to identify as to what I am doing wrong?

Please find attached.

Kind Regards,
Dayna