5 Replies Latest reply on Feb 16, 2012 7:00 AM by Fadi Elmasri

    How to Calculate Average for a data set coming from multiple tables of the same database? (more details below)

    Fadi Elmasri

      Tables (same Data source -excel file attached)

      -          Calls : Main table -

      -          Call Details : joined to the Calls table using Inner Join (1-to-1)

      -          Call Rating: joined to the Calls table using left  Join (0-to-n or 1-to-n)

      -          Discounts : joined to the Calls table using left  Join (0-to-n or 1-to-n)

       

      What I am trying to do:

      I have a report where I display the overall Calls Duration Average as a reference line on a call details table (tab#1 on the attached workbook) and also, I do display the overall Calls Duration Average as one value by just averaging the Call Duration values (tab#2 on the attached workbook).

       

      The issue:

      the two calculated overall Calls Duration Average values DO NOT match on the two tabs.

       

      Cause:

      I believe what is causing this behavior is the fact there is two (0-to-n or 1-to-n) left joints which cause the data to inflate (please see tab#3 on the attached workbook).

       

      I am trying to find a way to filter out those redundant call duration values. I need to be able to pick only one call duration value for each callID and be able to average them as a single measure.

      I have made some trails, but not fully working  (got the average but still displaying the CallIDs). Please see tab#4 on the attached workbook).

       

      Please let me know if you need any more details about the issue.

       

      Your help in this issue is much appreciated in Advance.

       

      Best Regards

      Fadi