I'm having troubles getting a line chart to come correctly in PerformancePoint using Dashboard Designer based on a custom MDX query.
We are attempting to display the last 6 months of the current year compared to the same 6 month period last year. I have the query written and it returns the data we are expecting.
SELECT NONEMPTY([Invoice Date].[Fiscal Week of Year].[Fiscal Week of Year])
ON COLUMNS
,NONEMPTY({[Invoice Date].[Hierarchy].[Fiscal Year]
* [Measures].[Amount]}) ON ROWS
FROM [Sales]
This returns the correct data.
We create a Line Chart to display the data. We get [Amount] (in dollars) on the y-axis and the Week of Year. The Week of Year is essentially just numbers so the current week number is 22 (because we use a non-Calendar Fiscal Year). We then have a year based time series, which allows us to view this year's Week 22 numbers vs. Week 22 in the previous year.
Our problem is that the last 6 months crosses the "boundary" of a year. And because of this our x-axis does not have a "proper" chronological sort. Our bottom axis looks like (I've simplified the number of weeks):
1 | 2 | 3 | ... | 20 | 21 | 22 | 48 | 49 | 50 | 51 | 52 |
Notice the jump between 22 and 48. This is numerically correct however chronologically this line chart should be:
48 | 49 | 50 | 51 | 52 | 1 | 2 | 3 | ... | 20 | 21 | 22 |
I know why this happens. The Fiscal Week of Year is not part of our [Invoice Date] hierarchy.
Our Hierarchy was Fiscal Year => Fiscal Quarter => Fiscal Month => Fiscal Date
So we fixed that and added the week to the hierarchy
Fiscal Year => Fiscal Quarter => Fiscal Month => Fiscal Week of Year => Fiscal Date
And the new MDX query looks like:
SELECT NONEMPTY([Invoice Date].[Hierarchy].[Fiscal Week of Year])
ON COLUMNS
,NONEMPTY({[Invoice Date].[Fiscal Year].[Fiscal Year]
* [Measures].[Amount]}) ON ROWS
FROM [Sales]
Well this fixed our sort issue because now the Week of Year has context as to what year Week 22 belongs to... but this presents a different problem
Because of that new context our chart now won't overlap the two different time series and instead presents them in back-to-back because they don't share a simple number anymore they now have Year + Week (even though the chart only displays the Week number)
So the chart now looks like the following
48 | 49 | 50 | 51 | 52 | 1 | 2 | 3 | ... | 20 | 21 | 22 |48 | 49 | 50 | 51 | 52 | 1 | 2 | 3 | ... | 20 | 21 | 22 |
Notice how the sort is correct chronologically but two years worth of weeks now come out along the x-axis side-by-side.
Hopefully this long-winded explanation helps explain the problem. It seems like we can either have overlapping time series with the "wrong" sort OR we can have the proper sort with the weeks but lose the ability to compare year over year.
What I want is for us to get the proper sort, so:
48 | 49 | 50 | 51 | 52 | 1 | 2 | 3 | ... | 20 | 21 | 22 |
But still be able to have the 2 different years overlap with each other.
Or perhaps put another way, I want the Week of Year to get its sorting based on the Year but also to then "forget" that context when returning data so that the Line Chart will allow the weeks to overlap by number. I'm stumped as to how to pull this off.
Ok I have an answer to the issue. The following MDX query will return the [Amount] with the Fiscal Years on the Rows and each column will be the Week of Year. The Week of Year will "sorted" by how the weeks occur chronologically. That's really a horrible description better to see it.
WITH SET [Weeks] AS
EXTRACT([Invoice Date].[Dashboard Hierarchy].[Fiscal Week of Year], [Invoice Date].[Dashboard Hierarchy])
SET [Week of Year] AS
INTERSECT(STRTOSET("{" + GENERATE( [Weeks]
,"[Invoice Date].[Fiscal Week Of Year].&[" + [Weeks].CURRENT.NAME + "]"
,",") + "}"), [Invoice Date].[Fiscal Week Of Year].MEMBERS)
SELECT [Week of Year] ON COLUMNS
,{([Invoice Date].[Fiscal Year].[Fiscal Year], [Measures].[Amount])} ON ROWS
FROM [Sales]
When you run the query you will get results like the following
Note that the columns are 51, 52, 53, 1, 2, 3 which is correct for the range of data I've selected (6 months to date). So in my example 6 months ago was the 51st week of that year.
Also notice that there is data that overlaps on week 51 for both 2012 and 2013. This is important if you want the line chart in PerformancePoint content to actually compare these data points. Here's the resulting chart.
Here's what the query is doing
WITH SET [Weeks] AS
EXTRACT([Invoice Date].[Dashboard Hierarchy].[Fiscal Week of Year], [Invoice Date].[Dashboard Hierarchy])
This is using the Extract function to remove all the members from the Fiscal Week of Year level from the Date hierarchy. This returns a set that can be used in the next calculated member.
INTERSECT(STRTOSET("{" + GENERATE( [Weeks]
,"[Invoice Date].[Fiscal Week Of Year].&[" + [Weeks].CURRENT.NAME + "]"
,",") + "}"), [Invoice Date].[Fiscal Week Of Year].MEMBERS)
Here's where most of the work is taking place. So starting from the "inside" and working our way out.
The Generate function is generating a comma-separated string based on the previously created Weeks calculated member. This string is actually being built to look like the [Fiscal Week of Year] member that contains just the weeks and is not part of a date hierarchy. This is important because you cannot return the weeks as part of a hierarchy to the line chart or it won't allow week 51, for example, from 2012 and 2013 to overlap because they are different members.
The new comma-separated string is then converted to a set using the StrToSet function.
Last but not least because there could be multiple week 51's for example, I'm Intersecting the results to return a unique list.
The new calculated member Week of Year is then return ON COLUMNS
I am always open to suggestions on how to accomplish the same thing but simpler because it does seem like I've created a Rube Goldberg MDX query but I just couldn't find any examples on the whole internets on how to accomplish this thing.