8 Replies Latest reply: Feb 20, 2013 9:48 PM by Huy Van RSS

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

    Huy Van
      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_
          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
            Huy Van
            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
              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
                Huy Van
                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
                  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
                    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
                      Huy Van
                      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
                        Huy Van
                        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