Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.