Oracle Fusion Data Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to properly average the differences in dates by month?

Question
23
Views
0
Comments
Caroline Blanchard
Caroline Blanchard Rank 6 - Analytics Lead

We are looking to create a new measure which will subtract the dates 'Requisition Line Approval Date' from 'PO Creation Fiscal Date'. Then we would like to Average the result by a monthly basis. The goal would be to have a bar chart with monthly average for the difference of the dates.

We are able to get the proper result only if 'PO Creation Fiscal Date' is included in a table and only the grand total shows the proper average.

The proper average we're looking for is the '-7.67' in the left table versus the '0.51' on the right table. Seems like the only way to get the average is to have the dates included, is there any way to get the right table to show the proper value without having the date field?


Here is the calculation being used. (The ExtractDay is used to convert to a timestamp)