avg vs sum/total
-
are these the same?
Enrolments Within Date Range is
sum( {
NonEmptyCrossJoin(
{[Start Date].firstchild.firstchild.firstchild:[Start Date].[2002].[Quarter 4].[December]},
{[End Date].[2000].[Quarter 2].[June]:[End Date].lastchild.lastchild.lastchild}
) } , [Measures].[Total Enrolments])
Is this function
Sum( {
NonEmptyCrossJoin(
{[Start Date].firstchild.firstchild.firstchild.firstchild:[Start Date].[2002].[Quarter 4].[December].[31]},
{[End Date].[2000].[Quarter 2].[June].[1]:[End Date].lastchild.lastchild.lastchild.lastchild}
) } , [Measures].[COE Duration])/[Measures].[Enrolments Within Date Range]
the same as
Avg( {
NonEmptyCrossJoin(
{[Start Date].firstchild.firstchild.firstchild.firstchild:[Start Date].[2002].[Quarter 4].[December].[31]},
{[End Date].[2000].[Quarter 2].[June].[1]:[End Date].lastchild.lastchild.lastchild.lastchild}
) } , [Measures].[COE Duration])
because i get different results.
Steve? Keith?
anyone?
Thanks
-
I would have thought that they should be the same if the Total Enrollments equals one (1) for each of the intersections of the start and end dates. If each start and end date combination can have a value > 1, then they probably shouldn't come to the same value.
In the 2nd fn you are getting an average similar to sum(COE Duration) / intersected_cell_count.
So if the intersected_cell_count is a different number to [Enrollments within date range] (which it will be if [enrollments...] intersections can be > 1), then the results returned should be different.
i think
. it made sense when i wrote it...Steve.
-
No they are not. AVG uses the count of the non-empty members only to get the denominator. Where SUM/Count will uses the count of all members in the denominator set.
Example:
Jan 1 = 100
Jan 2 = 100
Jan 3 = <NULL or EMPTY>
Jan 4 = 100
Avg = 300/3 = 100
Sum/Count = 300/4 = 75
Avg only includes members with values.
Hope that helps.
Steve Hughes
Magenic Technologies
. it made sense when i wrote it...Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply