I am using the LastPeriods Function to SUM the Last 12 periods at a month level:
MEMBER [Measures].[Last 12 Months] As
'SUM(LastPeriods(12, [Time].[Calendar Year].[Year].[2006].[Q4-06].&[December]), [Measures].[Total TEUS]) '
This should equal out to be [Time].[Calendar Year].[Year].[2006] but for some reason, the totals are different.
I thought it might be an issue with solve order so I took all other calculations out and it is still incorrect.
I am not sure what to look for now in fixing this problem. Is my syntax structure wrong here? Hopefully it is something small I am missing here.
Regards,
-Troy
What is the aggregation function for [Measures].[Total TEUS] - is it a "sum" measure?|||Deepak,
Actually, this issue was a user error on my part. Turns out I was comparing it with a different Time Dimension.
The numbers are actually coming out correctly. Sorry for that. The problem I am seeing now is when using this member and a SET for the TopCount() function.
For Example:
WITH
MEMBER [Measures].[LastPeriod] AS
'SUM(LastPeriods(12, [Time].[Calendar Year].[Year].&[2006].&[Q4-06].&[December]), [Measures].[Total TEUS]) '
SET [TopShipline] AS
'NONEMPTY({TOPCOUNT([Ship Line].[Shiplines].[SHIPLINE].Members, 5, [Measures].CurrentMember)})'
SELECT {[Measures].[LastPeriod]} ON COLUMNS,
[TopShipline] ON ROWS
From [Account Level Aggregates]
WHERE ([Domestic Companies].[Domestic Company].[Company Name].[Some Company])
The results are as follows:
LastPeriod
ShiplineA 772.0
ShiplineB 7,738.5
ShiplineC 3,976.7
ShiplineD 2,158.1
ShiplineE 4,707.9
ShiplineA should not be in the results here. If I change to TopCount count to 6. The result is correct, minus ShiplineA.
LastPeriod
ShiplineA 772.0
ShiplineB 7,738.5
ShiplineC 3,976.7
ShiplineD 2,158.1
ShiplineE 4,707.9
ShiplineF 1,660.0
TopCount() for other dimensions that use this time dimension have similar results. This is only when using LastPeriods().
If I use say:
SET [TopPorts] AS
'NONEMPTY({TOPCOUNT([US Port].[US Port Name].Children, 5, [Measures].CurrentMember)})'
LastPeriod
PortA 2,690.7
PortB 5,036.4
PortC 6,124.4
PortD 5,542.2
PortE 513.7
In this case, PortE does not belong and the sort order of my measure is not as it should be. Again, changing the count to 6 from five brings the correct fifth Port, PortF
|||Let me add one last piece to this and this is this apears to only be an issue when my [Domestic Company] dimension is used in the where clause:
WHERE ([Domestic Companies].[Domestic Company].[Company Name].[Some Company])
If I use a different dimension in the WHERE clause, the results are correct.
This is a somewhat large, flat dimension with about 600,000 members.
|||Keeping in mind that the named set will be computed only in the context of the "where" slicer axis, which doesn't include a measure, you should explicitly specify [Total TEUS], like:
SET [TopShipline] AS
'NONEMPTY(TOPCOUNT([Ship Line].[Shiplines].[SHIPLINE].Members, 5, [Measures].[Total TEUS]))'
Thanks for reply. I have resolved this. What I needed to do was use the Measure for my TopCount() function that I created as my calculated member.
MEMBER [Measures].[Last 12 Months] AS
'..........'
So, instead of:
SET [TopShipline] AS
'NONEMPTY( {TOPCOUNT( [Ship Line].[Shiplines].Children, 5, [Measures].[Total TEUS])})'
I needed to use:
SET [TopShipline] AS
'NONEMPTY( {TOPCOUNT( [Ship Line].[Shiplines].Children, 5, [Measures].[Last 12 Months])})'
