Create a daily, weekly, monthly and quarterly average for all measures

hi,

What's the best way to create an average for all time grains across all measures?

At the moment, I've got a calculated measure for each measure, which itself uses two calculated measures:

Code Snippet

DayCount =
count(descendants([DATE TIME].[Calendar], ,LEAVES))

MonthCount =
count(descendants([DATE TIME].[Calendar], [DATE TIME].[Calendar].[Month]))

...

Code Snippet

SumSales =
sum([DATE TIME].[Calendar], [Sales])

SumUnits =
sum([DATE TIME].[Calendar], [Sales Units])

...

Code Snippet

AvgDailySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[DayCount])

AvgMonthlySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[MonthCount])

...

Thanks in advance,

If you search this forum on "AVERAGE" and "TIME" you'll find a bunch of solutions addressing this issue.

B.

|||

Hi mmmman,

For the days counting and using it for avarages caculations you should create additional measure group that use the time dimension table as fact table with a measure that counts days.

If you play futher, for examle to base your calculations on count of business days, set in fact table 0 for weekend days and 1 for work days then made a sum measure on it.

The proposed approach has following advatages: is multiselect aware, hasn't performance drawbacks.

More about day counting you cat take from Mosha's blog

http://www.sqljunkies.com/WebLog/mosha/archive/2007/05/27/counting_days_mdx.aspx

where another approaches are also described.

Create a daily, weekly, monthly and quarterly average for all measures

hi,

What's the best way to create an average for all time grains across all measures?

At the moment, I've got a calculated measure for each measure, which itself uses two calculated measures:

Code Snippet

DayCount =
count(descendants([DATE TIME].[Calendar], ,LEAVES))

MonthCount =
count(descendants([DATE TIME].[Calendar], [DATE TIME].[Calendar].[Month]))

...

Code Snippet

SumSales =
sum([DATE TIME].[Calendar], [Sales])

SumUnits =
sum([DATE TIME].[Calendar], [Sales Units])

...

Code Snippet

AvgDailySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[DayCount])

AvgMonthlySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[MonthCount])

...

Thanks in advance,

If you search this forum on "AVERAGE" and "TIME" you'll find a bunch of solutions addressing this issue.

B.

|||

Hi mmmman,

For the days counting and using it for avarages caculations you should create additional measure group that use the time dimension table as fact table with a measure that counts days.

If you play futher, for examle to base your calculations on count of business days, set in fact table 0 for weekend days and 1 for work days then made a sum measure on it.

The proposed approach has following advatages: is multiselect aware, hasn't performance drawbacks.

More about day counting you cat take from Mosha's blog

http://www.sqljunkies.com/WebLog/mosha/archive/2007/05/27/counting_days_mdx.aspx

where another approaches are also described.

Counting weekdays in a month

I am trying to create a query that has a bunch of billing information in it. Everything is billed monthly. They want a column that calculates the count of days of the week only in each month. Does anyone know how to do this?

SELECT count(weekday?), (p.serv_mo_dt, count(distinct c.Pgm_Clnt_ID) as Client_Count, c.site_id As SiteID, b.brd_nm,
avg(unit_billed_qy) as Avg_units, avg(ccms_line_am) as Avg_Amount_paid
FROM VCCS_CLIENT c INNER JOIN
VCCS_Paid_claim p ON c.Pgm_Clnt_ID = p.Pgm_Clnt_ID AND c.Site_ID = p.Site_ID, vccs_site_brd_xref b
WHERE (c.Pgm_Clnt_Katrina = '1' or c.Pgm_Clnt_Rita = '1') and p.inactv_fl = '0' and p.serv_mo_dt > = '9/1/2005'
and b.site_id = c.site_id
and c.inactv_fl = '0' and fund_cd BETWEEN '700' and '799'
group by p.serv_mo_dt, c.site_id, b.brd_nm

I would use a calendar table:

http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/948d50fc4b94699

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de