OAD: Best Practices for Calculating Averages Across Different Time Periods

ashraf817

Hello,

I have a calculated column in the database that shows the average total per day, (sum patient invoices total / number of patients). This results in all rows for a given day displaying the same value in the column.

I now need to reuse this calculated column to aggregate the average totals on a monthly or yearly basis. However, I realize that my initial approach may have been flawed. The current calculation is only available as an attribute, which has limited my ability to fully utilize it in visualizations. And modifying this calculation from a daily to a yearly basis does not seem to be a solution.

I would appreciate any guidance on how to better structure this calculation to allow for more flexibility in aggregating and visualizing the data across different time periods.

Thank you.

SteveF-Oracle

Hi @ashraf817 ,

To add to Gianni's thorough response.

Any way, I just wanted to add to your knowledge, by sharing some self-service examples/tutorials from time series calculations. This does not specifically answer your question, but provides additional usage examples.

https://blogs.oracle.com/analytics/post/time-travel-made-easy-working-with-time-series-functions-in-oracle-analytics

BalagurunathanBagavathy-Oracle

@ashraf817

You can create level-based measures in the RPD / semantic model at quarter and year level and use them to create these calculations.

ashraf817

@BalagurunathanBagavathy-Oracle

Sorry for my ignorance but what is "level-based measures in the RPD"? I'm completely new to working with SQL and analytics and i use Oracle Analytics Desktop

my main reference is https://docs.oracle.com/en/middleware/bi/analytics-desktop/tutorials.html and it is very general and not in depth

BalagurunathanBagavathy-Oracle

@ashraf817 Can you please let me know if you are building your dataset using Oracle Analytics Desktop directly based on the database table?

Gianni Ceresa

Hi @ashraf817 ,

Because you are using Oracle Analytics Desktop, you don't have access to the semantic model (semantic model = RPD).

In your case, how about not doing the average calculation in the database? Because that average is the one giving you issues as you did calculate at a fixed level (that's what the database can do: nothing forbid you from creating a monthly and yearly average too).

You say that your result is SUM(invoices) / COUNT(patients), if that's the formula you need at all time level (day, month, year), just define your invoice and patients measures in your dataset, then you can add a calculation to do SUM() / COUNT() : that should give you an average column that adapt to the time level of your analysis (visualization).

Or what is the role of the time in the calculation? (how did you calculate that average in your database? What is the SQL behind it?)