Best Of

@BrianRowe / @GrantSmith

Neither of you are 'wrong' but it is inaccurate to say "it doesn't do average of an average"

i'd agree "it's misleading to take the average of an average" but it can be done.

this distinction is important because in many situations, new developers get tripped up about logic that's applied before or after the aggregation. I.e. at the row level vs. after the GROUP BY clause has been applied.

-- row level calculation (average of an average)

select
avg( colA/colB )

from table
group by ...

in the above example we are taking an average at the row level (a / b) and then aggregating it again after the group by. on most of days of the week we'd agree that's wrong.

-- aggregate calculation (the 'mathematically correct' approach)
sum(colA) / sum(colB)
from table
group by ...

here there all the math happens AFTER the group by clause. this is usually 'right'.

i'm spending time on this b/c i've seen people try to write

CASE WHEN DATE < today()-30 THEN sum(colA) / sum(colB) END 

this is 'wrong' b/c usually we want the CASE statement evaluated BEFORE aggregation (i.e. i only want activity older than 30 days). but b/c the inner function contains the aggregation (sum), the aggregation already happend (i.e. i've taken the average of all my data and lumped it into one number). it's impossible to test if the activity occured more than 30 days ago at this point.

to calculate "only activity that occured 30 days ago" you have to apply the logic on the row level.

sum(case when ... then amount) / sum(case when ... then denominatorAmount)

usually if the dataflow isn't bugged (as @GrantSmith suggested), if the dataflow won't run it's b/c it's already running.

you can write formulas in the GROUP BY tile too.

but as @GrantSmith pointed out, a Formula tile cannot have any aggregations (MAX, SUM, COUNT)

@EWold - You don't need to include UE_OrgUnitID in the second part of the ETL tile.

You only want to aggregate the three time values that you listed earlier.

QA_TimeStarted -> Maximum

QA_TimeCompleted -> Maximum

CA_DateAccess -> Maximum

This will take your data and provide you with a single row of data for each unique grouping of

• UE_OrgUnitID
• OU_MasterCourses_CourseName
• OU_CourseIsActive

Each row will contain the most recent value for each of the times listed above.