Issue while creating analysis using Max and Avg functions in new calculated measure.

JITHU JOSE

Summary:

We are trying to create a analysis for sales order processing lead time, but we are facing issue as the new calculated measure using avg function is not giving correct expected values.

Content (required):

For the order processing lead time analysis, we are comparing the order creation date and invoice dates and then taking the time in between these two to arrive at the order processing lead time. However, as there are multiple invoices for a same sales order, we used "Max" function on the invoice date attribute to get the latest invoice date for a particular sales order. Then we created a new calculated measure and took the avg of difference of max of invoice date and the creation date to get avg lead time over a given time period. however, the result given by OTBI is incorrect values.

The requirement is that for a single sales order when there are multiple invoices, we need to get the latest invoice date and use it to calculate the processing lead time. Please guide on how to resolve this issue and get the correct result.

formula used in otbi calculated measure: Avg(MAX(CAST("Fulfillment Line Detail Details"."Billing Transaction Date" AS DATE))- CAST("Fulfillment Lines - General Details"."Creation Date" AS DATE))

Version (include the version you are using, if applicable):

Oracle Fusion Cloud Applications 22D (11.13.22.10.0)

Code Snippet (add any code snippets that support your topic, if applicable):

Jahnavi-Oracle

Hi @JITHU JOSE , If we apply the 'by' function (at sales order level) along with the MAX it would give the max invoice data and creation date for a sales order.

For ex:

Avg(MAX(CAST("Fulfillment Line Detail Details"."Billing Transaction Date" AS DATE) by SALES_ORDER)- CAST("Fulfillment Lines - General Details"."Creation Date" AS DATE)by SALES_ORDER)

Hope this helps, let me know how it goes.

JITHU JOSE

@Jahnavi-Oracle Thanks a lot. It worked

Jahnavi-Oracle

Good to know @JITHU JOSE . Thanks.