This discussion is archived
5 Replies Latest reply: Jun 17, 2010 10:07 AM by 631379 RSS

Convert Period Dimension value to Numeric value

740913 Newbie
Currently Being Moderated
Hi,

I have a requirement in my report - ("YTD Actual" column divided by "Period" numeric value)
for this report Period dimension is set to prompt for value, if January is selected then numeric value should be 1, If Quarter1 is selecteed then numeric value should be 3, my problem is how to convert Period values (Quarter1, Quarter2, Quarter 3, Quarter4, Jan, Feb, Mar ...) to numeric values (Quarter1= 3, Quarter2 = 6, Jan =1, Feb = 2, Mar = 3...). I am new to Hyperion FR please help!

Thanks,
Gracie
  • 1. Re: Convert Period Dimension value to Numeric value
    642338 Journeyer
    Currently Being Moderated
    Hi Gracie

    If you are using Hyperion Planning or Essbase then I tend to use the concept I picked up from the add-on modules for Hyperion Planning (CapEx & Workforce) where they use a Dynamic Calc member in the database called 'Cal TP_Index' / 'Fisc TP_Index'.

    Formula for 'Cal TP_Index' is:
    IF (@ISMBR("Jan"))
    1;
    ELSEIF (@ISMBR("Feb"))
    2;
    ELSEIF (@ISMBR("Mar"))
    3;
    ...etc........
    ELSEIF (@ISMBR("Q1"))
    3;
    ELSEIF( @ISMBR("Q2"))
    6;
    ...etc........
    ELSEIF (@ISMBR("YearTotal"))
    12;

    For 'Fisc TP_Index' you simply restate the above replacing Jan with whatever your first fiscal accounting period is, Quarters and YearTotal also need to change, i.e. if you year runs April - March (common in the UK) then Apr = 1, May = 2, Mar = 3, Q1 = 6, Q2 = 9, YT = 3 etc...

    Hope this helps
    Stuart Game
    www.analitica.co.uk
  • 2. Re: Convert Period Dimension value to Numeric value
    740913 Newbie
    Currently Being Moderated
    Hi StuartGame,

    Good day!
    Thanks so much for the information!
    Just a follow up question is this doable in Financial Reporting without updating anything on Essbase side, btw we are using version 9.3.1.

    Thanks,
    Gracie
  • 3. Re: Convert Period Dimension value to Numeric value
    642338 Journeyer
    Currently Being Moderated
    Hi Gracie

    I don't think so, I think that the IfThen function would be your only possibly chance but this is limited in FR from memory, I think you can only evaluate numeric conditions and return numeric true/false results.

    I can understand you not wanting to add to your db but it really isn't difficult, and as it is 'dynamic calc' (assuming your Accounts dimension is dense) it doesn't add real overhead to performance.

    Hope you get it sorted
    Stuart Game
    www.analitica.co.uk
  • 4. Re: Convert Period Dimension value to Numeric value
    Vladislav Newbie
    Currently Being Moderated
    Hi Stuart,
    I have same issue but with HFM (not planning). Is it possible to convert "Jan", "Feb"..."Dec" members to "31.1.2010", "28.2.2010",..."31.12.2010" (and how to handle bissextile year?).
    I will wolcome any idea. Maybe using specific "alias" member?
    Thank you
    Vladislav
  • 5. Re: Convert Period Dimension value to Numeric value
    631379 Pro
    Currently Being Moderated
    There is a function for HFM that returns the period number of the Period dimension.

    The end dates of periods are not contained within HFM, hence they cannot be retrieved systematically. I have solved this problem is very slick way but I don't give it away for free.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points