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.
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?
try something like this:
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
Thx Much!
Got it to work with:
tail (periodsToDate([Package].[Time].[Time].[Time], lead([Period Starting Week], (?parm_PeriodLength? -1))), #prompt('parm_PeriodLength', 'integer')#)
Try also the last periods function.