Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Discover how organizations are unlocking new revenue streams: Watch here
cancel
Showing results for 
Search instead for 
Did you mean: 
james
Creator III
Creator III

Dates selected automatically

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

I wanted to make your life easier... Smile 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


View solution in original post

11 Replies
Not applicable

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.

Anonymous
Not applicable

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


Not applicable

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).

james
Creator III
Creator III
Author

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

Anonymous
Not applicable

I wanted to make your life easier... Smile 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


james
Creator III
Creator III
Author

PERFECTO!Big Smile

james
Creator III
Creator III
Author

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...

Anonymous
Not applicable

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))")

james
Creator III
Creator III
Author

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?