Search code examples
ssasmdx

Is it possible to write an MDX query of the format "last x days of days of week"?

This is from a heavily manually maintained report that I am trying to automate a bit view an SSAS cube.

The report contains daily sales, and, among other things, a measure called "last 4 's". E.g., For Friday, October 16 the measure was the average sales over the last 4 Fridays.

Is there a way to construct this in MDX in way that can be placed in a calculated measure in an SSAS cube?

ps--In response to whytheq's question, yes, the date dimension includes day of week, which is an integer in which Sun = 1, Mon = 2 and so on to Sat = 7.

I see that I was slightly ambiguous above. By "last 4 Fridays" above I meant the 4 Fridays immediately preceding October 16, not the most recent 4 Fridays.

Solution

  • Agreed with whytheq, that more information may help us to create optimal solution. Anyway:

    Solved if you have only flat days hierarchy:

    +All
-2015/01/01
-2015/01/02
...
-2015/12/31
...

    Logic could be like this:

    1. rank all days

    2. split by weeks

    3. calculate last 4 for every day type

    4. show result for every selected member

    Example of flat hierarchy [Report Date].[Report Date].[Day] calculation:

    with
member [Measures].[AllDaysRank] as Rank([Report Date].[Report Date].CurrentMember,[Report Date].[Report Date].[Day].Members)

member [Measures].[WeekDay] as ([Measures].[AllDaysRank]-(Int([Measures].[AllDaysRank]/7)*7))

set [Last4Set0] as TopCount(Filter([Report Date].[Report Date].[Day].Members,[Measures].[WeekDay]=0),4,[Measures].[AllDaysRank])
set [Last4Set1] as TopCount(Filter([Report Date].[Report Date].[Day].Members,[Measures].[WeekDay]=1),4,[Measures].[AllDaysRank])
set [Last4Set2] as TopCount(Filter([Report Date].[Report Date].[Day].Members,[Measures].[WeekDay]=2),4,[Measures].[AllDaysRank])
set [Last4Set3] as TopCount(Filter([Report Date].[Report Date].[Day].Members,[Measures].[WeekDay]=3),4,[Measures].[AllDaysRank])
set [Last4Set4] as TopCount(Filter([Report Date].[Report Date].[Day].Members,[Measures].[WeekDay]=4),4,[Measures].[AllDaysRank])
set [Last4Set5] as TopCount(Filter([Report Date].[Report Date].[Day].Members,[Measures].[WeekDay]=5),4,[Measures].[AllDaysRank])
set [Last4Set6] as TopCount(Filter([Report Date].[Report Date].[Day].Members,[Measures].[WeekDay]=6),4,[Measures].[AllDaysRank])

member [Measures].[Last4Measure] as
case [Measures].[WeekDay]
    when 0 then sum([Last4Set0],[Measures].[Count])
    when 1 then sum([Last4Set1],[Measures].[Count])
    when 2 then sum([Last4Set2],[Measures].[Count])
    when 3 then sum([Last4Set3],[Measures].[Count])
    when 4 then sum([Last4Set4],[Measures].[Count])
    when 5 then sum([Last4Set5],[Measures].[Count])
    when 6 then sum([Last4Set6],[Measures].[Count])
end

select {[Measures].[Count],[Measures].[AllDaysRank],[Measures].[WeekDay],[Measures].[Last4Measure]} on 0
,[Report Date].[Report Date].[Day].Members on 1
from [DATA]

    Result (Count, AllDaysRank, WeekDay, Last4Measure):

    20151001    10  740 5   35
20151002    10  741 6   39
20151003    8   742 0   37
20151004    12  743 1   42
20151005    13  744 2   42
20151006    12  745 3   39
20151007    10  746 4   36
20151008    8   747 5   35
20151009    6   748 6   39
20151010    11  749 0   37
20151011    10  750 1   42
20151012    7   751 2   42
20151013    8   752 3   39
20151014    6   753 4   36
20151015    9   754 5   35
20151016    11  755 6   39
20151017    11  756 0   37
20151018    10  757 1   42
20151019    14  758 2   42
20151020    8   759 3   39
20151021    11  760 4   36
20151022    4   761 5   35
20151023    16  762 6   39
20151024    5   763 0   37
20151025    10  764 1   42
20151026    8   765 2   42
20151027    11  766 3   39
20151028    9   767 4   36
20151029    14  768 5   35
20151030    6   769 6   39
20151031    10  770 0   37

    If you have week hierarchy or some properties (not to calculate day numbers), it would be easier.

    UPDATE (weekly attribute is present):

    Here is script for weeks, but please create weekday->day hierarchy first, e.g:

    All
+1
-2015/01/01
-2015/01/08
...
+2
-2015/01/02
-2015/01/09
...

    Code has code tricks I'll explain further:

    with
member [Measures].[Week Day INFO] as [Report Date].[Week Day].Properties( "Report Date Week Day" )

member [Measures].[Last4Measure] as
/* if there are no empty 4 weeks for the first dates with data, take smaller size */
 iif([Report Date].[Week Day].CurrentMember.Lag(3).Parent.Member_Key <> [Report Date].[Week Day].CurrentMember.Parent.Member_Key
,iif([Report Date].[Week Day].CurrentMember.Lag(2).Parent.Member_Key <> [Report Date].[Week Day].CurrentMember.Parent.Member_Key
,iif([Report Date].[Week Day].CurrentMember.Lag(1).Parent.Member_Key <> [Report Date].[Week Day].CurrentMember.Parent.Member_Key
,sum({[Report Date].[Week Day].CurrentMember},[Measures].[Count])
,sum({[Report Date].[Week Day].CurrentMember.Lag(1):[Report Date].[Week Day].CurrentMember},[Measures].[Count]))
,sum({[Report Date].[Week Day].CurrentMember.Lag(2):[Report Date].[Week Day].CurrentMember},[Measures].[Count]))
/* end of fixing, which could be necessary */

/* calculation part */
,sum({[Report Date].[Week Day].CurrentMember.Lag(3):[Report Date].[Week Day].CurrentMember},[Measures].[Count]))

select
{[Measures].[Count],[Measures].[Week Day INFO],[Measures].[Last4Measure]} on 0
,[Report Date].[Report Date].[Day].members on 1
from [DATA]

    Week Day INFO measure is for result viewing only, not necessary in calculations.

    Exact code to calculate is really simple: sum({[Report Date].[Week Day].CurrentMember.Lag(3):[Report Date].[Week Day].CurrentMember},[Measures].[Count]))

    But it's possible, that you don't have empty or senseless days, which is necessary for this calculation! Since it uses .lag(3), and once we try to calculate for the VERY first, second or third weeks, it will take last members from the previous weekday, e.g. to calculate .lag(3) for the Second Wednesday ever (at the beginning of your Date dimension), it will take 2nd Wed, 1st Wed, Last Tue, Pre-last Tue, which is unacceptable, so I've added decreasing lag-level by checking Parent name (since Parent is WeekDay number from the hierarchy we've already created).

    Sure thing, you'll use AVG instead of SUM. I used SUM to simplify checking an answer. It's dynamical, not for the very last member only. It depends on current member. LastNMembers