CODEWITHSUNDEEP
timemdx
user1513693
user1513693

Reputation: 1

MDX: Using named set in aggregate (last 7 days)

I have built a DataCalculation dimension that should have a time based calculated. It should sum for each measure the last 7 day, parallel period etc. I have built a named set for last 7 days: 

CREATE  SET CURRENTCUBE.[Last 7 days]
AS {
StrToMember(Cstr("[Dmn_Dates].[date calc].&["+
Cstr(Format(Now(),"yyyy-MM-dd")) +"T00:00:00]"))
:
StrToMember(Cstr("[Dmn_Dates].[Date calc].&["+
Cstr(Format(Now()-7,"yyyy-MM-dd")) +"T00:00:00]"))
};

And then tried to use it on a calculated member: create member currentcube.[Dmn_ DateTool].[Date Tool].[Last 7 Days_] as 

aggregate(
{
StrToMember(Cstr("[Dmn_Dates].[date calc].&["+
Cstr(Format(Now(),"yyyy-MM-dd")) +"T00:00:00]"))
:
StrToMember(Cstr("[Dmn_Dates].[Date calc].&["+
Cstr(Format(Now()-7,"yyyy-MM-dd")) +"T00:00:00]"))
}
,([Dmn_ DateTool].[Date Tool].[regular],measures.LeadCounter))
, visible = 1;

It doesn't work. What I get is a calculated memeber [Last 7 days] that show the same value as the regular value.

Thanks reading until here.

Upvotes: 0

Views: 1751

Answers (2)

Benoit
Benoit

Reputation: 1993

When you use the Range operator in an expression like member1: member2, member1 must come before member2 in hierarchical order. Otherwise the range operator will return an empty set.

Moreover you have to be sure that your expressions like StrToMember(Cstr("[Dmn_Dates].[date calc].&["+Cstr(Format(Now(),"yyyy-MM-dd")) +"T00:00:00]")) really returns an existing member: null:null returns the empty set.

Upvotes: 1

Marc Polizzi
Marc Polizzi

Reputation: 9375

I would check the generated set validity and that it contains the right members; sth like:

select [Last 7 days] on 0 ...

Perhaps you've to reverse the from/to : Now() - 7 : Now instead of Now() : Now() - 7 ...

Upvotes: 1

Related Questions