I have a requirement of using a corporate calendar that is different from the usual calendar where weeks/months/years are customised. I need to find some way to accomodate this calendar in Omniscope so that all standard date related formatting/sorting/grouping still works.
Converting date fields into text wouldn't work and I tried the date arithmetic a bit, but so far no luck. It would be really good to know if someone has worked on simillar task and how did you address this.
So you have a custom calendar where the week numbering starts from a given date in a given year, and the named months don't correspond with the 1st of the month.
For example for the custom year "2010/11", the year begins July 26th, and weeks are numbered accordingly, so Week 1 begins July 26th. "August" begins on 26th July and runs for precisely 4 weeks; etc.
I would suggest the best way to deal with this is to use dates in Omniscope as normal (internally represented as elapsed milliseconds since 1970), but to use formulae to convert the gregorian dates to your dates as category/text values or "year start" and month/week numerical values.
For example, the formula field "My Year" might be:
IF(
internalDate >= TEXTTODATE("26-Jul-2011"), "2011/12 and later",
internalDate >= TEXTTODATE("26-Jul-2010"), "2010/11",
internalDate >= TEXTTODATE("26-Jul-2009"), "2009/10",
"2008/09 and earlier"
)
or "My Year Start" might be:
IF(
internalDate >= TEXTTODATE("26-Jul-2011"), null,
internalDate >= TEXTTODATE("26-Jul-2010"), 2010,
internalDate >= TEXTTODATE("26-Jul-2009"), 2009,
null
)
and the same principle for months. The formulae could get quite large, but providing only at most a few years are spanned by the data, this should be manageable.
Weeks should be easier. You can use something like the following to get the week number in the custom calendar:
( NUMVALUE(DATETOTEXT(internalDate, "D")) - NUMVALUE(DATETOTEXT(TEXTTODATE("26-Jul-2011"), "D")) ) / 7
You'll need to do a bit more than this to handle boundaries between years, etc., but this should get you started.
DATETOTEXT(internalDate, "D") => the day in the year for [internalDate], as a text value
NUMVALUE(...) => converts text to a number value
We subtract to handle the week number offset
/ 7 => convert a day into a week number