# Reporting and Analytics for ERP

## Get Involved. Join the Conversation.

This is a public Forum  public

## Topic

OTBI HOW TO TURN MEASURE COLUMN INTO ITD YTD PTD
Topic posted January 11, 2019 by , last edited January 14, 2019, tagged Fusion Project reporting, OTBI
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.

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

Xavier

Document:
Document:

## Comment

• 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

• Hi Avesh

Xavier

• 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

• 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

• Hi Xavier,

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

Regards,

Avesh Ganodwala

• 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

• 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

• 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)

Regards,

Avesh Ganodwala

• 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.

• 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,