5 Replies Latest reply: May 9, 2013 7:50 AM by gauravnankar RSS

    Calculating To Date functions manually without Time series function

    933203
      Hi,

      Below is my requirement.

      We have a measure column (Ship To Budget Amount). The lowest grain level for this measure is at Month level and not day level.

      So now my client wants this measure to be calculated both YTD and AGO. But since this measure is not at day level(Start_dt and End_dt) i am unable to use To date and Ago functions.

      Hence i need to calculate it manually. Can some one please help me by telling how to achieve this.

      Can we write any function/formula to get the to date and ago values.

      Your help is deeply appreciated and will be marked asap.
        • 1. Re: Calculating To Date functions manually without Time series function
          937325
          Hi,

          use timestamp function for to date and ago date


          for to date use the below function at month level supose u have 10 years data means 120 months so

          TIMESTAMPADD(SQL_TSI_MONTH, -120, CURRENT_MONTH)


          for ago if u need to compare with previous month


          TIMESTAMPADD(SQL_TSI_MONTH, -1, CURRENT_MONTH)


          upadet me on this.


          mark if helpful/correct.


          Thanks
          • 2. Re: Calculating To Date functions manually without Time series function
            933203
            Thanks for your reply.

            Will check it and will update on it.

            And one question , can you explain me what will function do, what is this used for 'SQL_TSI_MONTH'.
            • 3. Re: Calculating To Date functions manually without Time series function
              gauravnankar
              Hi Both,

              TimeStampADD will only give you the Month names for the previous years. It won't give you the measure values (Ship To Budget Amount in this case) for those previous months.

              What you can do by being a bit cheeky is - create a logical column in your Time Dimension. Use CAST('01/' || '<Month Column>' || '<Year Column>' AS DATE) in the logical formula. What you have essentially done is used the Month Start Date as the Date to form the leaf level key for your time dimension. Once you have that, you are free to use the AGO and ToDate functions in the rpd.

              Thanks,
              Gaurav
              • 4. Re: Calculating To Date functions manually without Time series function
                933203
                Hi,

                Thanks for your reply...



                What you can do by being a bit cheeky is - create a logical column in your Time Dimension. Use CAST('01/' || '<Month Column>' || '<Year Column>' AS DATE) in the logical formula.

                So you mean i should create a new logical column in time dimension table by using above cast function.


                What you have essentially done is used the Month Start Date as the Date to form the leaf level key for your time dimension.

                Can you elaborate this... if we apply to date or ago Bi Server use (Start_Dt and End_Dt in physical SQL), so how can we avoid this by creating the above new column
                • 5. Re: Calculating To Date functions manually without Time series function
                  gauravnankar
                  What you can do by being a bit cheeky is - create a logical column in your Time Dimension. Use CAST('01/' || '<Month Column>' || '<Year Column>' AS DATE) in the logical formula.

                  So you mean i should create a new logical column in time dimension table by using above cast function.

                  --> Yes

                  What you have essentially done is used the Month Start Date as the Date to form the leaf level key for your time dimension.

                  Can you elaborate this... if we apply to date or ago Bi Server use (Start_Dt and End_Dt in physical SQL), so how can we avoid this by creating the above new column

                  --> The BI Server will use CAST('01/' || '<Month Column>' || '<Year Column>' AS DATE) in the physical sql. Define your Monthly Measure column at the Month level of the Time Hierarchy you will create and the rest will be taken care by the BI Server. Give it a try, it should work. The only essential for the Ago and Todate functions to work is to have a proper Time Hierarchy defined with a unique leaf level key.


                  Thanks,
                  Gaurav