CODEWITHSUNDEEP
sqlreporting-servicesmdxanalysis
Rob
Rob

Reputation: 7217

MDX - need to calculate the average total over a month, excluding weekends

I am a complete newbie to MDX and have been asked to work out a calculation that I am finding difficult. I've read up the basics on the language and looked at online articles but struggling to find something which can help me.

I have a Measure of TotalLogins on a day to day basis.

I have a Dimension of Date, that has Date/Month/Year/DayOfWeek attributes.

What I need to do is get the average TotalLogins over a particular month, but with the caveat of excluding Saturdays/Sundays.

I've been playing with the AVG function, the EXCEPT function and the IIF function, but cant seem to hit the nail on the head.

Here's an example of what I've been attempting:

WITH 
MEMBER [Measures].[MyAvg] AS
(
    AVG(Descendants ([Dim Date].[Date].CurrentMember, 
                       [Dim Date].[Date]),
            [Measures].[UniqueVisitsDay])
)
SELECT 
      {[Measures].[MyAvg]} ON COLUMNS,
      {NONEMPTY([Dim Date].[Year Month].Members)} ON ROWS
FROM
      [MyCube];

This gives me an average per month, but doesnt allow me to specify a range of months to fall between and doesnt allow me to filter the totals so that Sat/Sun arent included.

Can someone point me in the right direction?

Thanks in advance!!

Upvotes: 0

Views: 1344

Answers (1)

Dan
Dan

Reputation: 10680

Try this:

WITH MEMBER [Measures].[MyAvg] AS
    AVG(
        EXCEPT([Dim Date].[Day Of Week].Members, 
           {[Dim Date].[Day Of Week].[Saturday],[Dim Date].[Day Of Week].[Sunday]}
        ), [Measures].[UniqueVisitsDay]
    )
SELECT { [Measures].[MyAvg] } ON COLUMNS,
    NON EMPTY { [Dim Date].[Year Month].Members } ON ROWS
FROM
    [MyCube];

If you want to limit your results to a specific date range, replace the 3rd row from the bottom with something like this:

    NON EMPTY { [Dim Date].[Year Month].[2013 November]:[Dim Date].[Year Month].[2014 January] } ON ROWS

(of course you might need to replace the member names with their actual names, or better yet, use the keys, for example: [Dim Date].[Year Month].&[201311])

Upvotes: 1

Related Questions