named set with a condition

I am having a little problem with returning 13 or 14 periods in a named set. Is it possible to have a condition in a named set

ie if there is not a string of "14" in the set of (last 13 periods) then return the set of 13 periods else return the set of 14 periods.

Thanks.
Mark.

Hi Mark,

generally named sets are static and will be computed ones in the scope they belong to.

Could you please provide your sample set expression and query that used it?

Best regards,

Vladimir

|||

Code Snippet

{LastPeriods(12,Filter([Accounting Week Calendar].[Accounting Period].Members, [Accounting Week Calendar].Currentmember.Properties("Current Period")<>"0").Item(0))}

This provides me with a set of 12 periods.

What I want is to return preferably as a named is a set of 13 or 14 periods. This is to give a Moving annual total. We will return 13 periods if all of the periods contain 4 weeks, we will return 14 periods if they dont. We know that it if there is a period 14 it will only ever contain 1 week.

Need some way to provide this from the cube rather than the users doing the arithmetic.

As a blunt method I could use an iif insode a named set if this is possible.

Thanks.
Mark.

Named set using LastPeriods (but not all of them)

To get the current period we have a named set Current Period. To get the last 13 periods we have a named set Last 13 Periods. We can aslo detect period that are not period 14.

Period 14 is an oddball period and I want to skip over it i.e Last 12 Periods will aways show data for the previuous 12 periods that were not periods 14s.

How do we link all of this to give the last 13 periods which were not a period 14?

Many thanks this is driving me bonkers.
Mark.

Code Snippet

CURRENT PERIOD
{
Filter([Alt Week Calendar].[Accounting Period].Members, [Alt Week Calendar].Currentmember.Properties("Period14")="1")
}

LAST 13 PERIODS
{LastPeriods
(13,Filter([Alt Week Calendar].[Accounting Period].Members,[Alt Week Calendar].Currentmember.Properties("Current Period")<>"0").Item(0))
}

NOT PERIOD 14
{
Filter([Alt Week Calendar].[Accounting Period].Members,[Accounting Week Calendar].Currentmember.Properties("Period14")<>"1")
}

Hi Mark,

Here's an example from Adventure Works which returns the last three days that aren't weekends:

Code Snippet

with member measures.demo as

generate(

tail(

exists(

[Date].[Date].currentmember.lag(5):[Date].[Date].currentmember

, [Date].[Day Name].&[2]:[Date].[Day Name].&[6])

,3)

, [Date].[Date].currentmember.name + " (" + [Date].[Date].currentmember.properties("Day Name") + ")", ", ")

select measures.demo on 0,

[Date].[Date].[Date].members on 1

from [Adventure Works]

What I'm doing here is finding the last five days, using an exists to get only the days from that set which are weekdays, then finding the last three days in the resulting set. The outermost Generate() is simply there to display the results of the expression in an easy-to-read way.

HTH,

Chris