November 25, 2013 at 9:02 am
Hello All, I'm looking for some help with getting a KPI Trend expression to return the correct results.
I have created a Calculated Member to return the qualifying Sales for the 6 months prior to the current period:
WITH MEMBER [Sales Previous 6 Months] AS
SUM( { PARALLELPERIOD( [Business Written Date].[Dates by Month].[Calendar Year Quarter],2,[Business Written Date].[Dates by Month].CurrentMember):
[Business Written Date].[Dates by Month].currentMember.prevMember } ,
([Fact Sales].[PP Permission Active].&[Yes],[Measures].[Fact Sales Count]))
When I use the Calculated Member in a KPI Trend expression, I get the correct results if I specify a single instance of a date or date period, e.g. 2013Q1 or 201006, but if I specify a range, e.g. 2012Q1 - 2012Q2, then the Trend is not correct.
What I want is for any date range selected by a user browsing the Cube, if the total qualifying sales (i.e. those where [Fact Sales].[PP Permission Active] = 'Yes) are greater than the total qualifying sales for the 6 months prior to the date range selected, the Trend will be upwards and vice-versa. For example:
User selects the date range 2012Q3 to 2012Q4
Let's say the total qualifying sales for that period is 10
The KPI Trend expression should use the [Sales Previous 6 Months] measure to return the total qualifying sales for the period 2012Q1 to 2012Q2 and check whether that figure is greater than, less than or equal to, 10
This is my Trend expression:
Case
When IsEmpty(KPIGoal("Low PP")) Then Null
// If there is no qualifying data for the previous period, the Trend should be positive provided that the KPIValue is greater than zero.
When ISEMPTY([Measures].[Sales Previous 6 Months]) = TRUE AND KPIValue("Low PP") > 0 Then 1
When [Measures].[Sales Previous 6 Months] < KPIValue("Low PP") Then 1
When [Measures].[Sales Previous 6 Months] = KPIValue("Low PP") Then 0
Else -1
End
I suspect that the problem is that CurrentMember is being set to the last member in the date range - 2012Q4 in the above example - and therefore the [Sales Previous 6 Months] member is going back 2 quarters from there which is 2012Q2 whereas I want it to take the first member in the date range - 2012Q3 - and go back 2 quarters to 2012Q1.
It must be a fairly common requirement to do this type of rolling date calculation, but the solution eludes me. Can anyone help?
Thanks
Lempster
November 26, 2013 at 5:04 am
Ok, I've thought about this some more and have decided that it doesn't make sense to do what I'm trying to do with the ParallelPeriod function so I'm using LastPeriods instead.
I've created 3 Calculated Members: [Current 6 Months Sales], [Current 12 Months Sales] and [Previous 6 Months Sales] (which is simply the second measure minus the first measure).
I can then use the following calculation for my Trend expression:
Case
When IsEmpty(KPIGoal("Low PP")) Then Null
When [Measures].[Previous 6 Months Sales ] < [Measures].[Current 6 Months Sales] Then 1
When [Measures].[Previous 6 Months Sales ] = [Measures].[Current 6 Months Sales] Then 0
Else -1
End
There may be more efficient ways to achieve the same goal, but this works for me. 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply