I'm still learning MDX and I want to conduct a rather complex (?) query. I've found each combination of Date
and Open hour time
where there has been any sales using:
SELECT NON EMPTY
{
[Measures].[Quantity]
} ON COLUMNS,
NON EMPTY
{
(
[Dim Date].[Calender].[Date].ALLMEMBERS
,[Dim Time].[Open Hour Bucket].[Open Hour Bucket].ALLMEMBERS
)
} ON ROWS
FROM
( SELECT
(
{
(
[Dim Store2].[Store Name].&[ActualStoreName]
,[Dim Date].[Calender].[2014]
)
}
) ON COLUMNS
FROM [iceCube]
)
My result set then becomes like this:
Quantity
2014-11-07 11:00 - 11:59 1
2014-11-07 12:00 - 12:59 10
2014-11-08 11:00 - 11:59 3
2014-11-08 12:00 - 12:59 2
2014-11-10 13:00 - 13:59 1
Which is what I want at this stage. However, getting from the above result set into a average between hour like this:
Average
11:00 - 11:59 2
12:00 - 12:59 6
13:00 - 13:59 1
is a Connection I don't understand how I would conduct.
I want to find the average sales per the year 2014 as I want to create a chart in SSRS using a specific week's sales as a column chart in SSRS, while having a line of the average sales for 2014 as a separate line. This also complicates things, as I need to have the first query (albeit on a week's basis, and not in 2014 as it is now), and 2014's average in the same data set, which I do not know how to do.
I am thinking that the query to find the average sales per 2014 must be conducted in SSAS
as a calculated measure, and then in SSRS
I can try to find this week's sales and the average of 2014 in the same data set somehow.
Does anyone have any idea with this?
Regards,
EDIT
Running GregGalloway's answer I've got this output:
Quantity YearlyHourAvg
2014-01-01 09:00-09:59 (null) #Error
2014-01-01 10:00-10:59 (null) #Error
. . . .
. . . .
. . . .
2014-12-31 21:00-21:59 (null) #Error
(where the spots represent that it continues like that over the row), i.e. the Query gives every Open Hour Bucket
value from the first to the last of January.
When I tried to run the Query, it actually worked using only With MEMBER AS...
and the original query. However adding the [Measures].[YearlyHourAvg]
in the Select
clause caused these problems.
I am currently testing this query in SSRS
and SSMS
.
EDIT2
Perhaps I was not clear in the original post, but I need the average to be on per *time interval*
basis, and not by per time *and* date
basis. I.e. I need the average sale of the time spans 09:00-09:59
,10:00-10:59..
.
Try the following. It should add a new column which is the average for that hour across the whole 2014 year. The average should be an average per day which had any sales during that hour. If you want a simple average by the number of days in 2014 please write back and we can discuss. If you have questions about the changes I made please ask.
WITH
MEMBER [Measures].[YearlyHourAvg] as
Avg(
Descendants(
[Dim Date].[Calender].[2014],
[Dim Date].[Calender].[Date]
),
[Measures].[Quantity]
)
SELECT
{
[Measures].[Quantity],
[Measures].[YearlyHourAvg]
} ON COLUMNS,
NonEmpty(
{
(
[Dim Date].[Calender].[Date].ALLMEMBERS
,[Dim Time].[Open Hour Bucket].[Open Hour Bucket].ALLMEMBERS
)
} ,
[Measures].[Quantity]
)
ON ROWS
FROM
( SELECT
(
{
(
[Dim Store2].[Store Name].&[ActualStoreName]
,[Dim Date].[Calender].[2014]
)
}
) ON COLUMNS
FROM [iceCube]
)