Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Xavier Cuinier
    OTBI HOW TO TURN MEASURE COLUMN INTO ITD YTD PTD
    Topic posted January 11, 2019 by Xavier CuinierBronze Medal: 1,250+ Points, last edited January 14, 2019, tagged Fusion Project reporting, OTBI 
    344 Views, 10 Comments
    Title:
    OTBI HOW TO TURN MEASURE COLUMN INTO ITD YTD PTD
    Summary:
    OTBI HOW TO TURN MEASURE COLUMN INTO ITD YTD PTD
    Content:

    I spent (as a newbie in OTBI) a couple of days trying to display the same column measure but with different time dimension (ITD, YTD and even PTD) within the same OTBI analysis. It's was pretty hard, a lot of things are possible in OTBI, the problem is that you have to find answers in multiple places (documentation, internet, MOS, etc) before you are able to solve this question.

    I hope the document attached will help you going forward

    NB : this document was originally an answer to this post : https://cloudcustomerconnect.oracle.com/posts/222212dce5

    Xavier

    Comment

     

    • Avesh Ganodwala

      Hi Xavier,

       

      Could you please help how did you calculate PTD value.. i am able to calculate ITD and YTD  but not able to get PTD value using Project Costing - Actual Costs Real Time subject area

       

      Regards,

      Avesh Ganodwala

      • Xavier Cuinier

        Hi Avesh

        Please send me your PTD formula

        Xavier

        • Avesh Ganodwala

          Hi Xavier,

           

          I am trying using below PTD formula.

           

          FILTER("- Expenditure Item and Cost Distribution Measures"."Cost" USING ("Fiscal Calendar"."Fiscal Date" >= TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH((@{AccountingDate}{date '2018-01-01'})) +1 , (@{AccountingDate}{date '2018-01-01'}))))

           

          Regards,

          Avesh Ganodwala

          • Xavier Cuinier

            Please try this (not forget to add the subject area name) :

            FILTER("Project Costing - Actual Costs Real Time"."- Expenditure Item and Cost Distribution Measures"."Cost" USING ("Fiscal Calendar"."Fiscal Date" >= TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH((@{AccountingDate}{date '2018-01-01'})) +1 , (@{AccountingDate}{date '2018-01-01'}))))

            It should be working fine, it does for me

            Xavier

            • Avesh Ganodwala

              Hi Xavier,  

              still not working.. Is it possible for you to share the SQL of the analysis please..

               

              Regards,

              Avesh Ganodwala

              • Xavier Cuinier

                Hi Avesh,

                As discussed the issue came from the column used as parameter which is "Fiscal Calendar"."Fiscal Date"

                I have also attached to this topic two examples of OTBI analysis based on actual costs and revenue real time subject area. This way everyone can easily test the outcome

                Xavier

                • Avesh Ganodwala

                  Hi Xavier,

                   

                  Thank you for all your help on this. I was following all the steps from the attached document. Once we follow all the steps we need to add particular column as Prompt as well.

                  This was the step i was missing.

                   

                  Once thank you for all your help.:)

                   

                  Regards,

                  Avesh Ganodwala

    • Avesh Ganodwala

      Hi Xavier,

       

      As per the above the YTD amount gets calculated from 1/Jan  to till date. However we have requirment to calculcated the YTD amount from 1/Apr to till date.

      Below is the columns formula we are using to calculated the YTD amount. 

      IFNULL(FILTER("Project Costing - Actual Costs Real Time"."- Expenditure Item and Cost Distribution Measures"."Cost" USING ("Fiscal Calendar"."Fiscal Date" >= TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM (@{AccountingDate}{date '2018-06-30'})) * -(1) + 1, (@{AccountingDate}{date '2018-06-30'})))), 0)

       

      Could you please help to write the formula so that it add the column value from 1/Apr.

       

      Regards,

      Avesh Ganodwala

       

       

    • Ross Romeo

      Can you do this while using the 'Accounting Period' criteria so as to be able to select something like '05-19' for May, 2019? Instead of selecting the specific day. 

       

      Thanks.

    • Vadiraja KV

      Hi Ross,

       

      We are not able to achieve the YTD through April to till date.

      Can you please guide to the formula.

      Formula currently used is

      IFNULL(FILTER("Project Costing - Actual Costs Real Time"."- Expenditure Item and Cost Distribution Measures"."Cost" USING ("Fiscal Calendar"."Fiscal Date" >= TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM (@{AccountingDate}{date '2018-06-30'})) * -(1) + 1, (@{AccountingDate}{date '2018-06-30'})))), 0)

       

      Regards,

      Vadi