Dynamic Selections with Advanced Search

[markmccoid]

I've been using Advanced Search in bookmarks to select the current year, current month, etc and it works great. But now I have a client that wants to select Current Fiscal Year.

Their Fiscal Year is based on a spreadsheet linked to the date table. For the life of me I can't figure out how to select the fiscal year dynamically.

For example if today's date is 12/30/2010, I can easily do =CalendarDate=Year(Today()) in my dynamic selection and get calendar year 2010 selected, however, this date falls into Fiscal Year 2011 and I'm at a loss on how to make this selection dynamic.

Anybody run across a similar scenario? Any ideas?

Thanks!!

[#2]

I am working with a client that has 2 calendars. Essentially, in the script, I added the Fiscal Calendar with a column named Calendar where the value = 'Fiscal'. Then I concatenated the 2nd calendar with the additional column where the value='Retail'. Then, on the dashboard, I added a multi-box for Calendar. On the Presentation tab, I clicked the box next to 'Always One Selected Value'. This way everything will toggle with only 1 formula.

Calendar:

SQL Select

'Fiscal' as Calendar,

*

From Data.Calendar;

Concatenate (Calendar)

SQL Select

'Retail' as Calendar,

*

From Data.Calendar_Retail;

Thanks,

Aline

[rwunderlich]

Take a look at using the YearStart() function. Assume the fiscal year starts in April.

=CalendarDate=Year(YearStart(Today(),0,4))