MDX Help: - Comparing Values in Two Max Time Periods within a larger Set of Time Periods to Populate an Indicator

Question

I'm brand new to MDX and need some help. In SSRS I have a dataset that pulls from an SSAS cube. The dataset always contains six months of data. What I need to be able to do is to compare a value for the max(timeID) with a value for the second max(timeID) and if the value for the max(timeID) > value for the second max(timeID) than the arrow goes up in the indicator, etc...

So for the dataset below I would subtract 20130201's Value which is 8 from 20130301's Value which is 10. The result would be a positive number and the indicator would be an upward pointing green arrow. If it was 0 it would be straight and if negative the arrow would be red and point down. I understand how to deal with the indicator - that's not an issue. It's the MDX I need help with.

20130201 8 20130301 10 20121201 4

I can write it in SQL and it would look like this.

    Select Item, case when sum(Time1ContentCount) > sum(Time2ContentCount) then 3 when sum(Time1ContentCount) = sum(Time2ContentCount) then 2 when sum(Time1ContentCount) sum(Time2ContentCount) then 1 end as Indicator, sum(Time1ContentCount) as Time1Count, sum(Time2ContentCount) as Time2Count from (Select timeID, dc.Item,  Case when timeID = (Select max(timeID) from FactUsage) then count(fu.Contentid) else 0 END as Time1ContentCount,  Case when timeID = (Select max(timeID) from FactUsage where timeID <>(Select max(timeID) from FactUsage)) then count(fu.Contentid) else 0 END as Time2ContentCount  from  factUsage fu  INNER JOIN dimContent dC on dc.ContentID = fu.ContentID  WHERE TimeID in (Select distinct top 6 timeid from factUsage order by timeID desc) Group by  timeID, Item) a group by Item

Thanks so much for your help!

Edit: I changed the statement to read as follows for the indicator.

WITH Member MEASURES.Indicator AS ( 
        IIF(( [Measures].[Activity], [Time].[Time ID].LastChild ) > 
   ( [Measures].[Activity], [Time].[Time ID].LastChild.PrevMember),3,
            (IIF(([Measures].[Activity], [Time].[Time ID].LastChild ) = 
                ([Measures].[Activity], [Time].[Time ID].LastChild.PrevMember), 2,1))))


SELECT {Measures.Indicator} on 0
FROM [DW]

It works when I run it as a query against the cube in SSMS but I tried to put it in the indicator and that doesn't work. Just adding the IIF statement doesn't work either. When I tried to add it into the query or the cube itself so I could just pull from there it errors out with an out of memory error.

How are you distinguishing between the different "max(timeID)" if they are all in one dataset? — StevenWhite, May 28 '13 at 18:05
That's part of the question I guess. Since I have six time periods in there and I'm only comparing two...I'm just not sure how to do that. — JCB, May 28 '13 at 18:59
in MDX he is navigating a dimension so "max(xxx)" would be XXX.LastChild — cairnz, May 28 '13 at 18:59
Why are you not using CurrentMember and PrevMember (this way your indicators will be historically accurate based on what you look at in your dataset). Using LastChild will make it always only show "most current indicator" no matter what slice of time you look at... — cairnz, Jun 03 '13 at 08:24

cairnz · Accepted Answer · 2013-05-28T20:52:47.380

I don't know how much you can edit in the MDX expression - or in your report builder, but to get the difference between two values in a series, you can create a measure (in your report) that is the difference between the CurrentMember and PrevMember. Since the time series (timeid) is sorted by the key, it will always be in the right order (or your schema and architecture needs a rework)

So basically, you can do :

WITH 
MEMBER MEASURES.GrowthTime AS ( 
        ( [Measures].[Value], [TimeID].CurrentMember ) - 
        ( [Measures].[Value], [TimeID].PrevMember )
)
MEMBER MEASURES.GrowthRatio AS (
        ( [Measures].[Value], [TimeID].CurrentMember ) /
        ( [Measures].[Value], [TimeID].PrevMember )
)
SELECT { Measures.Value, Measures.GrowthTime, Measures.GrowthRatio } on 0,
[TimeID].CHILDREN on 1
FROM Cube

This is pseudo as i don't know your cube structure. For TimeID you would want it like [DimensionName].[AttributeName].CurrentMember and PrevMember

MDX Help: - Comparing Values in Two Max Time Periods within a larger Set of Time Periods to Populate an Indicator

1 Answers1