RESOLVED: Relative Time Set

[adam_mc]

Dimension based on Transformer Cube

I am trying to create a set of Weeks based on Relative Time calculations and have run into issues.
User is to be prompted with Starting Fiscal Week and a Number of Weeks prompt.
I want to create a set based on those values.

So, if FW 201405 selected and 3 Weeks entered, set needs to be [201405], [201406], and [201407].

What I have so far is this (and please let me know if there is a far simpler way!!):

• tail (periodsToDate([Package].[Time].[Time].[Time], lead([Period Starting Week], (?parm_PeriodLength? -1))), ?parm_PeriodLength?)

And, I am getting the following message:

• "The prompt 'parm_PeriodLength' is used multiple times with conflicting data types."

However, my syntax appears correct, as if I just hard-code the index of the tail function (as below), I get valid results (but hard-coding is not an option).

• tail (periodsToDate([Package].[Time].[Time].[Time], lead([Period Starting Week], (?parm_PeriodLength? -1))), 3)

Any thoughts would be greatly appreciated.
Thanks in advance,
Adam.

[adik]

How is your time dimension organized? do you have the weeks split per month or years? or are all the weeks on the same level without a parent? if a user selects last week of 2013 and a number of 4 weeks what is the expected result?

[adik]

try something like this:

Code Select Expand

head(except(members([time].[time].[weeks]), periodsToDate([time].[time].[weeks], #prompt('p_start_week', 'token')#)), #prompt('p_no_of_weeks', 'integer')#)
where [time].[time].[weeks] is your fiscal weeks level

[adam_mc]

Thx Much!

Got it to work with:

tail (periodsToDate([Package].[Time].[Time].[Time], lead([Period Starting Week], (?parm_PeriodLength? -1))), #prompt('parm_PeriodLength', 'integer')#)

[CognosPaul]

Try also the last periods function.