topic Select Macro in QlikView

Select Macro

Wed, 18 Jan 2012 15:13:40 GMT

Hi All,

What I want to do is REALLY simple.. so I must be thick as I can't get this to work!!

When I load my data I have a field [Report Date] linked to all my data, then I use calendar to create a MaxDate field.

I want to create a macro (to link to a button) to select all the [Report Date] data linked to this month, i.e. month of the MaxDate field.

All I want to say is Year(MaxDate) = Year[Report Date] and Month[MaxDate]=Month[Report Date] but am not sure of syntax to use in the macro, can anyone point me in the right direction?

Many thanks,

Phil

Select Macro

Anonymous — Thu, 19 Jan 2012 01:06:49 GMT

Phil,

Assuming your only date field is the [Report Date], and assuming the format is MM/DD/YYYY, you can use action (no need for macro) like this:
Action: select in field
Field: [Report Date]
Search string:
=num(month(max([Report Date])),'00') & '/??/' & num(year(max([Report Date]))

It will select all values in the [Report Date] which are in the same month as the max [Report Date]

Re: Select Macro

Thu, 19 Jan 2012 08:05:11 GMT

Thanks Michael,

We're using v8.5 so I'm not sure if actions will do the trick

Kind Regards,

Phil

Select Macro

Anonymous — Wed, 08 Feb 2012 18:04:19 GMT

Phil,

This should work:

sub SelectDates
set rd = ActiveDocument.Fields("Report Date")
e = ActiveDocument.Evaluate("=monthstart(MaxDate)")

rd.Select ">=" & e

end sub

If there are dates later than max date in the list, it will be a little different:

sub SelectDates
set rd = ActiveDocument.Fields("Report Date")

e = ActiveDocument.Evaluate("=monthstart(MaxDate)")
l = ActiveDocument.Evaluate("=monthend(MaxDate)")
rd.Select ">=" & e & "<=" & l
end sub

(Hope I didn't miss much...)