Calculate "normalized" weekly sums from daily data

[#1]

I'm trying to calculate a normalized weekly sum based on number of business days in the week.  The rule is:

((sum of daily data for the week) / number of business days in the week) * 5

So if Monday is a holiday there would be 4 BD's in the week.

I've joined the date data with the fact data in the load so i now have one table with:

FactField1, FactField2, ..., Date, Week #, number_bds_in_week, Daily_Amount

So I think i want to calculate a "normalized" weekly sum according to that formula above, then divide that sum across the days in that week so that when you sum it in a table it gives you the right number.

Any ideas on how to approach this?  Am i overcomplicating?  I'm assuming it's better to do it in the load rather than post-load.

Thanks

Justin

[#2 · Accepted solution]

Think i got it myself:

Aggr(sum(quantity/number_bds_in_wk) * 5, Week)

That is essentially a sum of the averages * 5, grouped by week.  The beautiful mystery of the Aggr() function.