I've had a request from my user to add a custom period range to the data they look at, as the months they use aren't the calendar month, i.e. December would run from 28/11/2011 to 01/01/2012. But they would like to select December and these dates would be selected.
I have a table that contains the following:
Period Start Date
Period End Date
Period No (i.e. 1,2,3,etc).
The data I am looking has does have a relationship to this data, but contains the field 'Date'. So ideally, if they select Dec, they would select the Date in another table.
They do also still need the originally calendar, so they would have another tab where Dec would select the Calendar December date range.
Currently, the way I have done it is to use various expressions such as the following:
[code]
month(MakeDate(year(if(month(Date) = 'Dec', year([Period Start Date]),year([Period End Date]))),if([Period Start Date] <= DateAND[Period End Date] >= Date, [Period No]))) &'-'&if(month(MakeDate(year(if(month(Date) = 'Dec', weekyear([Period Start Date]),weekyear([Period End Date]))),if([Period Start Date] <= DateAND[Period End Date] >= Date, [Period No]))) <> null(),if(month(Date) = 'Dec', weekyear([Period Start Date]),weekyear([Period End Date])),null())
[/code]
So this would produce 'Dec-2011' and select everything within the range.
Does anyone know of a better/easier way to achieve my requirements?
