LastPeriods() Function producing incorrect results

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])})'

LastNonEmpty and AverageOfChildren

Hello,

we are using SQL 2005 with SP1 and we have quite small cube (<1GB).

We have a couple of measures with aggregate function AverageOfChildren or LastNonEmpty.

End users have very poor performances while browsing cube using these measures.

When we replace agg. function with SUM, for example, performances become normal.

I saw simillar discusion on OLAP forum, but I did not understand what is the best recommendation for this situation?

Is this known issue, and how it can be avoided?

Best regards

Borko

Borko,

What's your aggregation strategy for this measure group? Queries to semi-additive measures are resolved by initially going down to granularity for the measure group's time dimension and subsequently computing the aggregate. Because of this, it's important to insure that you have an aggregation that includes the granularity attribute. If not already there, you can generally force the attribute to be included by setting the AggregationUsage property to "Full" in the cube editor. However, since this setting applies across all measure groups, you may want to set it, design aggregations for this measure group, and then rest the value.

I'm not positive that this will solve your issue, but this is where I would start.

-rob

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

sql

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

last 30 dates measures

Hi all,

I want to do a top / order by query like

select top 30 f.measure, d.date
from fact f
join date_dim d on f.date_key = f.date_key
group by d.date
order by d.date desc

you get the picture,

in MDX.

I know I can use now() but how do I get the dd/mm/yyyy format and use strtomember to put this on the rows axis?

also, I cannot get order() to order the dates axis (or any other dimension for that matter)

any ideas?

Thanks

Hi,

To convert the current date, you can use something like

StrToMember("[Period].[Calendar].[Day].&[" + format(DateAdd("d",-1,now()),"yyyyMMdd") +"]")

"[Period].[Calendar].[Day].&[" - represents the structure of your date dimension.

To get a 30 dates from the current date use something like -

WITHSET [30 Dates] AS {StrToMember("[Period].[Calendar].[Day].&[" + format(DateAdd("d",-1,now()),"yyyyMMdd") +"]"):StrToMember("[Period].[Calendar].[Day].&[" + format(DateAdd("d",-1,now()),"yyyyMMdd") +"]").lag(30)}

SELECT [Measures].[Value] on COLUMNS,

[30 Dates] on ROWS

FROM [CUBE]

Note, this query is only showing you, the measure you are interested in for the last 30 days.

Does this help?

When you want to order the dates, what exactly do you mean? Do you want to order them by a certain property or the value of your measures.

Thanks

Punita