Thank you for supporting the Cloud Customer Connect Community in 2024. It's a gift to work with you!

Look back
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

BI Publisher SQL Dividing a Sum by Count Distinct

edited Nov 29, 2019 2:51PM in Reporting & Analytics for B2C Service 1 comment

Summary

I am creating a new BI Publisher data model to analyze some expense report data. I need to divide a sum of values by a count(distinct) however, the result returned is showin as a tenth of the correct

Content

Report to analyze average time taken to approve expense claims

Values are:

total no. of reports = count(distinct expense_report_num) = 1978

total time to approve = sum(expense_submit_date - expense_final_approval_date) = 23348

Need to divide total time to approve by total no. of reports. So I am using the following calculation:

(SUM(eer.final_approval_date - eer.report_submit_date)) / COUNT(DISTINCT 

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!