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.