This discussion is archived
8 Replies Latest reply: Feb 20, 2013 7:48 PM by HuyVan RSS

How to compare a Date data with Current Year and Period Member on FIX

HuyVan Newbie
Currently Being Moderated
Hi experts,

I have a Project dim that each member is a Project (P01, P02...)
and Account dim that stores information of the each Project (Name, Start date, Finished Date...)
Finished Date member is in Date data type

So how can write a IF condition below in order to compare Project Finished Date with Current Year and Period members on FIX
FIX (@Descendants(Projects), Descendants(All Year), Descendants("Year Total")...)
IF (@CURRMBR(Period)->@CURRMBR(Year) < Project->FinishedDate)
Do something...
Else
Do something


Please help me on this. Sorry for my bad grammar. Please ask if there is anything unclear

Many thanks,
Huy Van.

Edited by: Huy Van on Jan 29, 2013 1:14 AM

Edited by: Huy Van on Jan 29, 2013 2:24 AM

Edited by: Huy Van on Jan 29, 2013 2:25 AM

Edited by: Huy Van on Jan 29, 2013 6:04 PM
  • 1. Re: How to compare a Date data with Current Year and Period
    _RahulS_ Guru
    Currently Being Moderated
    Please have a look at @DATEDIFF
    http://docs.oracle.com/cd/E17236_01/epm.1112/esb_tech_ref/calc_datediff.html

    Also at below post:
    Re: Days behaviour between two dates


    Cheers..!!
    Rahul S.
  • 2. Re: How to compare a Date data with Current Year and Period
    HuyVan Newbie
    Currently Being Moderated
    Thanks for quick response, Rahul. But what I need here is not comparing 2 Date data but comparing one Date data (Project->FinishedDate) with a intersection of current Year->Period member on FIX statement.

    I have just edited the code above for more clear.

    Any idea of how to do that?

    Edited by: Huy Van on Jan 29, 2013 2:34 AM

    Edited by: Huy Van on Jan 29, 2013 2:34 AM
  • 3. Re: How to compare a Date data with Current Year and Period
    Rowdy Journeyer
    Currently Being Moderated
    Essbase date calculation functions (@TODAY().@DATEDIF(),..) assume the date numbers are stored in seconds..but Essbase stores planning date datatype numbers in yyyymmdd format and they don't make any sense.

    You can use functions like @Round, @Truncate, @Remainder and temp. variables in Essbase (VAR) to calculate the numbers of months difference b/w the two dates. These difference can then be compared in the conditions (<0 or >=0) for your logic.

    - Ram Reddy
  • 4. Re: How to compare a Date data with Current Year and Period
    HuyVan Newbie
    Currently Being Moderated
    Tks Ram, so is there anyway to convert current Member of Year and Period on FIX Statement to numeric or Date ? Or we all we can is
    currYear = @Substring(@NAME(@CURRMBR("Year"), 2,3); // For FY13 return 13...

    IF (@NAME(@CURRMBR("Period")) == "Jan") currMonth = 1;
    ELSE IF (@NAME(@CURRMBR("Period")) == "Feb") currMonth = 2;
    ...
    and so on

    Is there any better way to do that?
  • 5. Re: How to compare a Date data with Current Year and Period
    Rowdy Journeyer
    Currently Being Moderated
    So the goal is to calculate the total number of months for each date on all possible lvl0 combinations. This will all be math logic. Alp from one of the previous links had a very good example for that.

    Without knowing much about your case, I am just putting this together theoretically.

    FIX(@RELATIVE(Products,0),@RELATIVE(Year,0),@RELATIVE(Periods,0)

    VAR SD1;
    VAR SD2;
    VAR SD3;
    VAR ED1;
    VAR ED2;
    VAR ED3;

    /* Start Date total number of months */
    SD1=@ROUND("Start Date"/10000,0);
    SD2=@REMAINDER(@ROUND("Start Date"/100,0)/100);
    SD3=SD1*12+SD2;

    /* Same thing for end date */

    "calc member"(
    IF(SD3<ED3)
    formula?
    ELSE
    formula?
    ENDIF;)

    ENDFIX;

    There is all possibility I might be missing something including the syntax.. This was an intention just to outline the logic.

    Hope that helps.
    - Ram Reddy
  • 6. Re: How to compare a Date data with Current Year and Period Member on FIX
    Jake Turrell Expert
    Currently Being Moderated
    I would approach this using the Essbase string CDF's. Download the last file on this page ("String.zip").

    http://www.oracle.com/technetwork/indexes/samplecode/essbase-sample-522117.html

    There's a readme in the zip file that tells you how to install the CDF's.

    These functions will allow you to do the following:

    1) Pull the Essbase date value (which is stored as a double) into a String.
    2) Use the substring function to parse the date (now a string) into its components (Years, Months, Day).
    3) Compare strings.

    Of course, it's not quite as simple as this. You're probably going to be comparing "01" to Jan, etc, but I've given you the general approach.

    Whenever I'm in a situation where I need to compare a numeric value in Essbase to a member name (something you just can't do with out-of-the-box Essbase), the Sting functions have gotten the job done.

    Hope this helps,
    - Jake
  • 7. Re: How to compare a Date data with Current Year and Period Member on FIX
    HuyVan Newbie
    Currently Being Moderated
    So the solution I'm following now is:
    1. Use either Ram/Jake solution to convert Data data to Numeric/Comparable datatype
    2. Use String Function, IF ... THAN convert current Year and Month member on FIX statement to Numeric/Comparable datatype
    and do the compare these two converted value
    Quite inconvenient, but it seem there's no better way

    Thanks for your supports,
    Huy Van.
  • 8. Re: How to compare a Date data with Current Year and Period Member on FIX
    HuyVan Newbie
    Currently Being Moderated
    Here is what I have done. Post for whom may concern later

    VAR FM; /* Finished Month of Project*/
    VAR FY; /* Finished Year of Project */
    VAR CM; /* Capture Current Month on FIX statments */
    VAR CY; /* Capture Current Year on FIX statments*/


    FIX ( @RELATIVE( "Year", 0), @RELATIVE( "Period", 0), @IDescendants( "Base Projects")....)

    FY = @ROUND( "TGHT"->"NA Contract"->"FY06"->"NA Period" / 10000, 0);
    FM = @MOD( @ROUND( "TGHT"->"NA Contract"->"FY06"->"NA Period" / 100, 0), 100);
    /* For FY13 return 13... */
    CY = @JgetDoubleFromString( @CONCATENATE( "20", @SUBSTRING( @NAME( @CURRMBRRANGE( Year, Lev, 0, 0, 0)), 2)));
    /* Set CM value based on currrent Period On FIX statement */
    IF ( @ISMBR( "Jan"))
    CM = 1;
    ELSEIF ( @ISMBR( "Feb"))
    CM = 2;
    .....
    ELSEIF ( @ISMBR( "Dec"))
    CM = 12;
    ENDIF


    IF ( CY < FY OR ( CY == FY AND CM < FM))
    Do something...
    ELSE
    Do something...
    ENDIF
    ENDFIX

    Edited by: Huy Van on Feb 19, 2013 11:10 PM

    Edited by: Huy Van on Feb 20, 2013 7:46 PM

Legend

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