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.

       

      Thanks

      Justin