Filter a member from Dynamic Set

Question

Hi,

I have a dynamic set named (Last 12 months) on date dimension which will bring last 12 finished months on a given date. For example as this month is march, the dynamic set will bring Mar 2011 to Feb 2012.

so to get any measure values for last 12 months i would use:

select

[Measures].[mymeasure] on Columns,

{[Last 12 months]} on Rows

from cube

I have another requirement to get last 11 months values for a measure.

My Question is can i use the existing dynamic set named "Last 12 months" by excluding the first item which is Mar 2011? If so how would i write my query without using another member or creating another named set?

Any suggestions?

Answer 1

Hi,

You write a query with an except and Head to get the last 11 months.

select

[Measures].[mymeasure] on Columns,

Except([last12months],Head([last12months],1)) on Rows

from cube

---

Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Answer 2

You can  have last 11 months by using :

[Last 12 Months].item(1) : [Last 12 Months].item(11)

Thanks,

--Ashim

Note : If this is helpful, Do not forget to mark as "Answered"

Answer 3

Thanks Ashim and Rakesh !!