creating a calculated member in mx
-
Hello,
please when I use the tail and nonempty function on rows it gives the correct value for the member (query below)
select
{[Measures].[Vol]} on columns,
tail(nonempty
( [Dates].[Month Short Name].[Month Short Name].allmembers),6).item(1) on rows
from
[ICS];
----------------------------
but when I try creating a calculated member with similar expression it gives the result for the whole set instead of the member (the query is below )
WITH MEMBER
[Measures].[Value]
AS
(tail(nonempty
( [Measures].[Vol],[Dates].[Month Short Name].[Month Short Name].allmembers),6).item(1))
select
[Measures].[Value] on 0
from
[ICS]
please any advice
-
You're doing two different things in the two queries.
try creating it as a WITH SET instead of WITH MEMBER
-
What I am trying to do is to pick a month(dynamically) in a set and construct a turple with it
for example
If I have a dimension of dimdate with English month name hierarchy
then I will like to get a set like the last two month like below
After which I will be able to choose a specific month
But i want to be able to construct it as a calculated column, say for the last six month(each) and will be able use another dimension like days on the rows.
I will appreciate your feed back.
-
Thanks ,
for the reply, what i did eventuaually was to create a set as advised, then from the set created i was able to create measures for any of the months i choose in the set using their positions item(), then i was able to use the other dimensions (days). so i could compare collections by months by days as well.
WITH
SET
[LASTSIXMONTHS]
AS
(tail(nonempty
( [Dates].[Month Short Name].[Month Short Name].allmembers),6))
MEMBER
MEASURES.LLL
AS
( [LASTSIXMONTHS].ITEM(0),[Measures].[Value])
MEMBER
MEASURES.LLK
AS
( [LASTSIXMONTHS].ITEM(5),[Measures].[Collections])
MEMBER
MEASURES.LLP
AS
( [LASTSIXMONTHS].ITEM(5).NAME)
SELECT
{MEASURES.LLL, MEASURES.LLK,MEASURES.LLP} ON 0,
[Days].[Days].[Days].ALLMEMBERS ON 1
FROM
(SELECT
[Days].[days].&[0]:[Days].[Days].&[60] ON 0
FROM
[ICS])
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply