mdx question

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.[200
40430])'
member [time].[wktd] as 'sum(time.[20040426]:time.[2004043
0])'
member [time].[monthtd] as
'sum(openingperiod([day],ancestor([20040430],[month])):time.[
;20040430])'
member [time].[lastmonth] as 'sum(time.[20040301]:time.[2004
0331])'
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]/[m
easures].[ECH_InCall]*100.00)'
set [timeset] as '{[time].[thisdate], [time].[wktd]
, [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[00], [measures].[15], [measur
es].[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.[200
40430])'
member [time].[wktd] as 'avg(time.[20040426]:time.[2004043
0])'
member [time].[monthtd] as
'avg(openingperiod([day],ancestor([20040430],[month])):time.[
;20040430])'
member [time].[lastmonth] as 'avg(time.[20040301]:time.[2004
0331])'
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]/[m
easures].[ECH_InCall]*100.00)'
set [timeset] as '{[time].[thisdate], [time].[wktd]
, [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[00], [measures].[15], [measur
es].[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):
[vbcol=seagreen]
With Member [Time].[ThisMonth] as
'Sum([Time].[1997].[Q2].[5]:[Time].[1997].[Q2].&
#91;5])'
Member [Time].[SumYTD] as
'Sum([Time].[1997].[Q1].[1]:[Time].[1997].[Q2].&
#91;5])'
Member [Time].[AvgYTD] as
'Avg([Time].[1997].[Q1].[1]:[Time].[1997].[Q2].&
#91;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[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!