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