Macro on open for Fiscal Year

[james]

All- any idea or recommendations for having a MACRO or something equivalent select the correct FY and correct FM based on a date? I have Fiscal Yearand Fiscal Month in the data model, however, i am stuck. Any ideas?

Below  is my MACRO for CY and selection of todays date, which works perfect for Calendar Year.

sub OnOpen
ActiveDocument.ClearAll
If ActiveDocument.Evaluate("Day(today())") = "1" and ActiveDocument.Evaluate("Month(today())") = "Jan" Then
set y=ActiveDocument.Fields("Year")
set m=ActiveDocument.Fields("Month")
y.Select ActiveDocument.Evaluate("Year(today())-1")
m.Select ">= 1" & "<=12"

else
If ActiveDocument.Evaluate("Day(today())") = "1" Then
set y=ActiveDocument.Fields("Year")
set m=ActiveDocument.Fields("Month")
y.Select ActiveDocument.Evaluate("Year(today())")
m.Select ">= 1" & "<" & ActiveDocument.Evaluate("num(month(today()))")

Else

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 if
end if
end sub

[#2]

James:

I dont think macro is needed for this case. This can managed with select in field action. Add select in field action and add FY as filed and search string as =Year(Today()-1).

Similarly for FM, trick is to make sure that expression return and field data formats are same.

Kiran.

[james]

I need the dashboard to automatically open up to the cprrectfiscal dates base don todays date..... I ahevnt used select in field action.. If my date is FiscalMonth, how owuld that work?

[#4]

Let me try to understand the scenario: there is an application which when opened should set the calendar filters on fiscal year and month based on today's date. Is that right?

Kiran.

[james]

Yes, on open, it should set the correct Fiscal Dates based on todays date. Thus, if the dates was 9.28.12, it would know thats October 2013, for example

[#6]

Enclosed a example where a field (year) is set based on todays date. Refer "on document open trigger".

Similarly identify the fields where the filters has to be set and apply filters on open.

Kiran.

[james]

Kirn, maybe im not making myself clear. I understand the year -1 functio however, FiscalYear won work that way. If todays date is 10/2/12, the Fiscal yearwould be 2013... Yet once we are in Jan - Sep 2013, the year would still be 2013... any idea are apprecaieted

[#8]

James:

For fiscal years the start date of the year is other than jan-1st. In that case you can use function like yearname to set the offset of the month.

In India we have Apr-Mar as Fyear. For getting Fyear function I use yearname(today(),0,4). 4 indicates apr is first month. This returns 2012-2013, on which string functions (like subfield) can be applied to get desired string.

You can find more in help section for yearname().

Regards,

Kiran.

[whiteline]

=if(Month(Today()<10), Year(Today()), Year(Today())-1)

[james]

Thanks WHiteline, but how would one go about adressing the correct month/ I.e Fiscal Month October runs from 9/28/12- 10/27/12?, based on todays date?