topic Dates selected automatically in QlikView

Dates selected automatically

james — Fri, 05 Jun 2009 19:53:24 GMT

Greetings-

I am wondering if anyone has had any luck using a macro or something else that knows that you are in 2009 or (Current Year) and that the month should be thru today. Thus when the app opens it should select 2009 (since we are presently in the year) as the year and Jan - Jun as the Month range.

Obviously as the year goes on it will know to select more dates based on today's date.

Currently we are saving the date range so when it is sent out via publisher, the date range is there; However this is not automatic and relies on someone every month changing the date range...

Any ideas? Tips? Suggestions

Thanks

Dates selected automatically

Fri, 05 Jun 2009 21:30:00 GMT

For your macro, you're going to want to create an OnOpen sub. Go to the Macros tab under Document Properties and there you can set OnOpen to one of your subroutines. This sub will obviously run when the document is opened.

There are multiple ways to get the current date in a QlikView macro. There is a good discussion of the different methods in this thread: http://community.qlik.com/forums/t/16651.aspx

In your sub, you're going to need to pull the current date using one of the above methods. Then you want to use

ActiveDocument.Fields("FIELD_NAME").Select 'Selection'

to make selections on your fields. For year, it's going to be a single selection. The months will be a little more complicated as you'll need to define a range. The specifics will depend on how your data is laid out.

Dates selected automatically

Anonymous — Fri, 05 Jun 2009 22:08:45 GMT

Or, you can do it on the Date level, so the year and month will be indirectly selected automatically:


sub OnOpen
 set d=ActiveDocument.Fields("Date")
 d.Select ">=" & ActiveDocument.Evaluate("YearStart(today())") & "<=" & ActiveDocument.Evaluate("today()")
end sub

Dates selected automatically

Fri, 05 Jun 2009 22:16:44 GMT

As I was writing my reply, I wondered about the best way to select a range of dates. I think Michael's suggestion is the way to go. It makes the Selection cleaner (and you only have to make one).

Dates selected automatically

james — Sat, 06 Jun 2009 00:41:56 GMT

Michael, that is nice!

But is there a way for it to actually Select the Year and The months in that Select statement? As of now this is what it looks like >=1/1/2009 <=6/5/2009

I was thinking it would be year 2009 and Months Jan, Feb, mar, Apr, May, Jun

Dates selected automatically

Anonymous — Sat, 06 Jun 2009 00:53:39 GMT

I wanted to make your life easier... OK


sub OnOpen
 set y=ActiveDocument.Fields("Year")
 set m=ActiveDocument.Fields("Month")
 y.Select ActiveDocument.Evaluate("Year(today())")
 m.Select ">= 1" & "<=" & ActiveDocument.Evaluate("num(month(today()))")
end sub

Dates selected automatically

james — Sat, 06 Jun 2009 00:58:02 GMT

PERFECTO!

Dates selected automatically

james — Wed, 01 Jul 2009 09:04:18 GMT

Micahel, I tried ACtiveDocument.Evaluate("Month(today())-1"), yet it wont work

any ideas

I want it to know that based on todays date, i.e. June, go back 1 month

I can write that same thing in the UI, but in the Macro it doesnt work...

Dates selected automatically

Anonymous — Wed, 01 Jul 2009 09:32:09 GMT

Well, month(today()-1) is the month of the yesterday day, that in most cases is the same as today's month, unless today is the 1st of a month.
To go back one month, you can use AddMonths(today(), -1). So, in macro:
ActiveDocument.Evaluate("month(AddMonths(today(), -1))")

Dates selected automatically

james — Thu, 30 Jul 2009 05:56:49 GMT

Michael, I was thinking and have been stumped on this,

sub StartDate

set y=ActiveDocument.Fields("Year")

set m=ActiveDocument.Fields("Month")

Select ActiveDocument.Evaluate("Year(today())")

Select ">= 1" & "<" & ActiveDocument.Evaluate("num(month(today()))")

end sub

how can I throw in an If statement onto this to tell it if the Month is January, go back one year and select all months or else do as is?

Dates selected automatically

Anonymous — Thu, 30 Jul 2009 06:46:51 GMT

You can use "if":
if <condition here> then
<your actions if condition is true>
else
<your actions if condition is false>
end if
I can't tell more because it's not exactly clear waht is your condition "the Month is January" (current Month? selected Month?), and what is "go back one year and select all months". Hope you can figure out from here.

Dates selected automatically

james — Thu, 30 Jul 2009 08:30:43 GMT

Michel, I was thinking thta based on todays date, it would evaluet the month and if todays date was in January, it would go back 1 year and select that year and all 12 months or else it would do the normal routine