i have a mdx in which in am using "sum" function to calculate WTD, MTD, YTD
etc
when i change i to "avg" function it does not give any difference in numbers
Is the "avg" being used - ?

select 1, * from openquery (ROP_OLAP, "
with
member [time].[thisdate] as 'sum(time.[20040430]:time.[20040430])'
member [time].[wktd] as 'sum(time.[20040426]:time.[20040430])'
member [time].[monthtd] as
'sum(openingperiod([day],ancestor([20040430],[month])):time.[20040430])'
member [time].[lastmonth] as 'sum(time.[20040301]:time.[20040331])'
member [time].[ytd] as 'sum(time.[20040301]:time.[20040430])'
member [measures].[00] as '[measures].[ECH_Acw_Time]'
member [measures].[15] as '[measures].[inbound_AHT]'
member [measures].[44] as '[measures].[inbound_contacts_per_hour]'
member [measures].[1003] as '[measures].[inbound_ACW]'
member [measures].[1004] as '[measures].[inbound_ATT]'
member [measures].[1006] as '[measures].[inbound_hold]'
member [measures].[1007] as '
iif([measures].[ECH_InCall]=0,0,[measures].[CAS_Ic_C]/[measures].[ECH_InCall]*100.00)'
set [timeset] as '{[time].[thisdate], [time].[wktd], [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[00], [measures].[15], [measures].[44],
[measures].[1003], [measures].[1004], [measures].[1006],
[measures].[1007]} on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])
")

--------- MDX with avg

select 1, * from openquery (ROP_OLAP, "
with
member [time].[thisdate] as 'avg(time.[20040430]:time.[20040430])'
member [time].[wktd] as 'avg(time.[20040426]:time.[20040430])'
member [time].[monthtd] as
'avg(openingperiod([day],ancestor([20040430],[month])):time.[20040430])'
member [time].[lastmonth] as 'avg(time.[20040301]:time.[20040331])'
member [time].[ytd] as 'avg(time.[20040301]:time.[20040430])'
member [measures].[00] as '[measures].[ECH_Acw_Time]'
member [measures].[15] as '[measures].[inbound_AHT]'
member [measures].[44] as '[measures].[inbound_contacts_per_hour]'
member [measures].[1003] as '[measures].[inbound_ACW]'
member [measures].[1004] as '[measures].[inbound_ATT]'
member [measures].[1006] as '[measures].[inbound_hold]'
member [measures].[1007] as '
iif([measures].[ECH_InCall]=0,0,[measures].[CAS_Ic_C]/[measures].[ECH_InCall]*100.00)'
set [timeset] as '{[time].[thisdate], [time].[wktd], [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[00], [measures].[15], [measures].[44],
[measures].[1003], [measures].[1004], [measures].[1006],
[measures].[1007]} on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])
")

Can you give an example of a Foodmart MDX query that illustrates your
issue? This query works as expected - the Sum (107,551) is 5 times the
Avg (21,510.20):

'Sum([Time].[1997].[Q2].[5]:[Time].[1997].[Q2].[5])'
Member [Time].[SumYTD] as
'Sum([Time].[1997].[Q1].[1]:[Time].[1997].[Q2].[5])'
Member [Time].[AvgYTD] as
'Avg([Time].[1997].[Q1].[1]:[Time].[1997].[Q2].[5])'
Set [TimeSet] as
'{[Time].[ThisMonth], [Time].[SumYTD], [Time].[AvgYTD]}'

Select {[Measures].[Unit Sales]} on columns,
{[Time].[1997].[Q1].[1]:[Time].[1997].[Q2].[5],[TimeSet]} on rows
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!