2

I have been working on creating a kpi that compares the rank of a customer on a specific date compared to the previous day. In turn you would be able to see if that customer has went up in rank or down in rank in terms of whatever the list was generated by. In my situation they are ordered by revenue.

I am able to rank my customers via the rank function easily enough and provide the report but when it comes to creating the kpi of comparing these ranks across days I am struggling in figuring out how this should be approached. The rank itself is not something stored as data it is something that I will need to create on the fly via the rank function.

Here is an example of my mdx query that I am using to create my initial starting report that provides me with rank of customers without a date splice:

WITH SET [RevRank] AS
ORDER (
    [Customer].[Customer Id].CHILDREN ,
    [Measures].[Revenue], BDESC)  
  MEMBER [Measures].[RANKRevenue] AS RANK([Customer].[Customer Id].CurrentMember, [RevRank] )

SELECT NON EMPTY { [Measures].[Revenue], [Measures].[Fact Order Count], [Measures].[RANKRevenue] } ON COLUMNS, 
  NON EMPTY TopCount( { ([RevRank] ) } , 100, [Measures].[Revenue]) ON ROWS 
FROM [DW]

From this I am attempting to splice in a specific date (day) and then compare that rank to a previous day within a kpi. So, starting off I am working on breaking this query up. I created a pre calculated set and pre calculated member to help me do this more easily. Now I am just trying to figure out how to create this set and member by a day and then I can at least produce a comparison between one day and the next.

01/26/2012 Update: Ok, I am a bit further down the road on this, but I am still having issues with getting the rank to pull into my query, the query below has nulls for the rankings. Hopefully someone can see the issue with this query.

WITH MEMBER  [Measures].[PrevDayRevenue] AS
( [Measures].[Revenue], ParallelPeriod ([Date Link].[PK Date].[PK Date],1))
SET [RevRankPrevOrder] AS
ORDER (
   [Customer].[Customer Id].Members ,
   [Measures].[PrevDayRevenue],
   BDESC)
MEMBER [Measures].[RANKRevenuePrevOrder] AS RANK([Customer].CurrentMember, [RevRankPrevOrder])
SET [RevRankCurrOrder] AS
ORDER (
   [Customer].[Customer Id].Members ,
   [Measures].[Revenue],
   BDESC)
MEMBER [Measures].[RANKRevenueCurrOrder] AS RANK([Customer].CurrentMember, [RevRankCurrOrder])

SELECT NON EMPTY { [Measures].[Revenue],  [Measures].[PrevDayRevenue], [Measures].[RANKRevenuePrevOrder], [Measures].[RANKRevenueCurrOrder] } ON COLUMNS,
NON EMPTY { ( [RevRankCurrOrder] ) } ON ROWS 
FROM [DW] 
WHERE {[Date Link].[PK Date].&[2012-01-08T00:00:00]}
Deduplicator
  • 44,692
  • 7
  • 66
  • 118
Bill Blankenship
  • 3,316
  • 6
  • 43
  • 73

2 Answers2

1

You can use the ParallelPeriod function to calculate the rank for the prior day. This will also need to be done on the fly.

Then you can just compare the two in your KPI value...

CASE
    WHEN [Customer Rank Yesterday] - [Customer Rank Today] > 0 THEN 1
    WHEN [Customer Rank Yesterday] - [Customer Rank Today] = 0 THEN 0
    ELSE -1
END
Bill Anton
  • 2,920
  • 17
  • 23
  • I have added in my mdx query that I am currently working with. As you have shown above I need to create a calculated member that I can use in the kpis. I will try to give parallelperiod function a shot as this was something I was attempting to use. – Bill Blankenship Jan 25 '12 at 22:37
  • I understand how I would review these calculated measures in the kpi itself. I am having more of a difficulty with creating the actual members that would be fed to the kpi. As of right now I have been unable to use the ParallelPeriod function to meet my goal. – Bill Blankenship Jan 25 '12 at 23:25
0

Here is my finished query, hope this helps someone else. :

WITH MEMBER  [Measures].[PrevDayRevenue] AS
( [Measures].[Revenue], ParallelPeriod ([Date Link].[PK Date].[PK Date],1))

SET [RevRankPrevOrder] AS
ORDER (
   [Customer].[Customer Id].CHILDREN ,
   [Measures].[PrevDayRevenue],
   BDESC)

MEMBER [Measures].[RANKRevenuePrevOrder] AS 
    RANK(
    [Customer].[Customer Id].CurrentMember,
    [RevRankPrevOrder])

SET [RevRankCurrOrder] AS
ORDER (
   [Customer].[Customer Id].CHILDREN,
   [Measures].[Revenue],
   BDESC)

MEMBER [Measures].[RANKRevenueCurrOrder] AS RANK([Customer].[Customer Id].CurrentMember, [RevRankCurrOrder])

SELECT NON EMPTY { [Measures].[Revenue],  [Measures].[PrevDayRevenue], [Measures].[RANKRevenuePrevOrder], [Measures].[RANKRevenueCurrOrder]  } ON COLUMNS,
NON EMPTY { ( [RevRankCurrOrder] ) } ON ROWS 
FROM [DW] 
WHERE {[Date Link].[PK Date].&[2012-01-10T00:00:00]}
Bill Blankenship
  • 3,316
  • 6
  • 43
  • 73
  • nice work! i came up with a similar solution but was having trouble getting the numbers to come out correctly after adding them to the MDX script. The problem ended up being that I needed to make the named sets "Dynamic" in the MDX Script. – Bill Anton Jan 27 '12 at 18:46
  • Thanks, I am having some huge issues getting this to work in powerpivot though......keep getting null to string errors, and have pretty much encased every possible statement with and IIF(isempty switch to zero statement. – Bill Blankenship Jan 27 '12 at 19:06
  • did you set the data type for the columns once the data was imported into powerpivot? – Bill Anton Jan 27 '12 at 19:38
  • I can't even get to that point, I put the mdx query in the query designer and click finish and before I get to the data being present it comes up and tells me that it cannot convert dbnull to string. I am sure that it has to do with the data but can't seem to stop the error from occurring. – Bill Blankenship Jan 27 '12 at 20:06
  • you may need to add formatting options to your calculated members in your query...or you could add them to the MDXscript and reprocess your cube...then just drag'n'drop into the graphical powerpivot source query designer – Bill Anton Jan 27 '12 at 20:09
  • Yeah, I wasn't sure how to handle this so I started trying other things. Adding in a simple query from other areas of the cube that aren't calculated members. I still get this error, so I am thinking I have an issue within my cube.....(data) – Bill Blankenship Jan 27 '12 at 20:26
  • Which is wierd because I was using power pivot with this cube at one time. – Bill Blankenship Jan 27 '12 at 20:26