1 Reply Latest reply: Jun 14, 2013 4:51 PM by Justin Martin RSS

    Calculate "normalized" weekly sums from daily data

      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.