Visokio website     Downloads     Video tutorials     KnowledgeBase  
How to handle custom corporate calendars in Omniscope - Visokio Forums
How to handle custom corporate calendars in Omniscope
  • VoteVote Up1Vote Down     indranildatta January 20, 2011 4:15PM
    Dear Omniscope Comrades,

    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.

    Thanks very much!
    Indranil Datta
    Invizua Limited - www.invizua.com - Dedicated to Media, Marketing Services and Market Research Organisations
    M: +44 7956 470 046 | E: indranil.datta@invizua.com | Skype: indranildatta1 | LinkedIn: www.linkedin.com/in/indranildatta | Twitter: @indranildatta

    Address: Studio 6, 36-42 New Inn Yard, London EC2A 3EY
  • 1 Comment
  •     steve January 21, 2011 12:13PM
    Indranil,

    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

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership