I am trying to get the day difference using CAPTION.
I am using a Transformer cube 10.2.1 with a Time Dimension at Business Day Level.
Ex.
The caption for Jan 2015 Business Day 16 is 201501 W 4 D 16 = [Day 1]
The caption for Jan 2015 Business Day 15 is 201501 W 4 D 15 = [Day 2]
The caption for Jan 2015 Business Day 14 is 201501 W 4 D 14 = [Day 3]
I created [Data Item1] attempting to get the day difference.
cast(substring(caption([Day 1]),14,2),integer) - cast(substring(caption([Day 2]),14,2),integer)
I created [Data Item1] that I added as a column in my crosstab.
case
when [Data Item1] = 1 then [Day 1]
when [Data Item1] = 2 then [Day 2]
else [Day 3]
end
The validation is failing with reason below:
MDO-ERR-0019 The members of dimension '[Test Cube].[Fiscal Time]' are not sorted, but function 'parallelPeriod' requires the members to be ordered. Please correct the model for this package and add sort specifications to the dimension and enable the 'allow member relative functions' option on the dimension.
All levels in Time Dimension are sorted. Please assist how to fix this error or please suggest another way how I could get the difference between the 2 days.
Thank you in advance.
Please help if there is any way I could retrieve the exact day from last year other than caption which I still can't make it work.
The problem is how our time dimension is setup.
Our time consists of Year, Quarter, Month, Week and Business Days
Year, Quarter and Month are based on a period field(ex 201512) in our own calendar table.
Week and day are 2 separate fields from our calendar table.
Just an ex. on how transformer categories looks like:
For Jan 2014, there are 22 Business Days
2014 Qtr 1 Jan Week 1 Day 1, 2
Week 2 Day 3, 4, 5, 6, 7
Week 3 Day 8, 9, 10, 11, 12
Week 4 Day 13, 14, 15, 16, 17
Week 5 Day 18, 19, 20, 21, 22
For Jan 2015, there are 21 Business Days
2015 Qtr 1 Jan Week 1 Day 1
Week 2 Day 2, 3, 4, 5, 6
Week 3 Day 7, 8, 9, 10, 11
Week 4 Day 12, 13, 14, 15, 16
Week 5 Day 17, 18, 19, 20, 21
I need a report that will compare for example Day 2 of 2015 and 2014
User is able to select 2015 Business Day.
I tried using parallelperiod or cousin functions to get 2014 day 2 but it is retrieving Day 3
Any suggestions on how to make this work?
Thank you in advance.
Creating relative time in your Transformer cube is the best way to go. Even if you get something based on caption manipulation to work it most likely won't aggregate properly.
We do have relative time for current day this year and same day last year. I am able to manipulate there to match the days. That is working fine.
For this report however, I need to allow the user to re-run based on Business day.
Thank you for replying.