Multiple measures in a dimensional crosstab report

[raj_aries81]

I'm working on a crosstab report that has Revenue by Current week and by Prior Year current week (aligned based on weekday name not date).



                     
      Current Week Day 1   Current Week Day 2   Current Week Day 3   Current Week Day 4   Current Week Day 5   Current Week Day 6   Current Week Day 7
Revenue                     
LY Revenue                     


For current week I have used descendants([Current Week],2) to get the current year's Week days onto the column area and then dropped Revenue to the Rows.For LY Revenue, I have created below two calculations and dropped it below the Revenue Measure but they doesn't seems to work as I see blank cells.



1) tuple([Revenue],parallelPeriod([Cube].[Date].[Date].[Day],364,currentmember([Cube].[Date].[Date])))

2) tuple([Revenue],lag(currentMember([Cube].[Date].[Date]),364))

Any help is really appreciated

Regards
Raj

[Ammus1234]

Try this.

total([Revenue] within set parallelPeriod([Cube].[Date].[Date].[Day],364,currentmember([Cube].[Date].[Date])
total([Revenue] within set lag(currentMember([Cube].[Date].[Date]),364))

[raj_aries81]

Thanks Ammus, tried implementing the logic you mentioned, but its not working.To be more clear and precise attached is what I'm looking for.

Try this.

total([Revenue] within set parallelPeriod([Cube].[Date].[Date].[Day],364,currentmember([Cube].[Date].[Date])
total([Revenue] within set lag(currentMember([Cube].[Date].[Date]),364))

[raj_aries81]

Thanks Ammus, tried implementing the logic you mentioned, but its not working.To be more clear and precise attached is what I'm looking for.

I have tried the below approach  -

1) Dragged a Crosstab Space with fact cells below Revenue
2) Changed the crosstab intersection define contents to 'Yes'
3) Created a calculation as below -
   tuple([Turnover],lag(linkMember(currentMember([Cube].[Week].[Current Week]),[Cube].[Date].[Date].[Day]),364))


This doesn't seem to work either, appreciate your attention and any information on this.

[Lynn]

Your columns have days in the current week but then you want to intersect those with a measure for a different time frame.

Do you have any relative time defined in your cube?

[raj_aries81]

Your columns have days in the current week but then you want to intersect those with a measure for a different time frame.

Do you have any relative time defined in your cube?

Hi Lynn,

Thanks for the reply.Yes I have the relative time categories.The challange is Date hierarchy and Current Week hierarchy are part of two different dimensions so linkMember is not an option to hook back to my main Date Hierarchy.

However, I've Last Week hierachy & Date hierachy and within the same dimension so somehow I was able derive the current week & measure value for the Last Year Current Week using the below Calculation -

Current Year Current week
lastPeriods(7,lag(linkMember(lastChild(closingPeriod([cube].[Date].[Last Week].[Week])),[cube].[Date].[Date].[Day]),-7))

Get the closing period of the Last Week and use linkmember to go to the main Date hierarchy then using lag to move down the hierarchy by 7 days to get the current week, finally use lastPeriods to get the current week on the report.


Last Year Current Week
total([Measure] within set lag(currentMember([Cube].[Date].[Date]),364))

This one is used to get the corresponding same Weekday of the last year.


This is working, now I can see both my CY Week and LY Week data. Not sure if there is a much efficient way of doing this.


Regards
Raj

[raj_aries81]

Hi Lynn,

Thanks for the reply.Yes I have the relative time categories.The challange is Date hierarchy and Current Week hierarchy are part of two different dimensions so linkMember is not an option to hook back to my main Date Hierarchy.

However, I've Last Week hierachy & Date hierachy and within the same dimension so somehow I was able derive the current week & measure value for the Last Year Current Week using the below Calculation -

Current Year Current week
lastPeriods(7,lag(linkMember(lastChild(closingPeriod([cube].[Date].[Last Week].[Week])),[cube].[Date].[Date].[Day]),-7))

Get the closing period of the Last Week and use linkmember to go to the main Date hierarchy then using lag to move down the hierarchy by 7 days to get the current week, finally use lastPeriods to get the current week on the report.


Last Year Current Week
total([Measure] within set lag(currentMember([Cube].[Date].[Date]),364))

This one is used to get the corresponding same Weekday of the last year.


This is working, now I can see both my CY Week and LY Week data. Not sure if there is a much efficient way of doing this.


Regards
Raj

I can see LY Current Week and CY Current Week values in the report. Now, I have to display the values for CY WTD and LY WTD. I have CY WTD & LY Week (not LY WTD) hierarchy in my relative time dimension, but I don't have LY WTD.


I'm using the below calculation to derive LY WTD -

aggregate(currentMeasure within set topCount([cube].[Week].[Current Week Last Year].[Day],count(1 within set [Cube].[Week].[WTD].[Day])))

I'm counting the number of days in the current Week ---> using topCount to count the same number of days from LY Current Week hierarchy --> then aggregating it.

Tried below two approaches -

I set'Define Contents' to Yes and adding the above calculation in the crosstab cell.
I even tried using a crosstab space with fact cells then set Define Contents to Yes and by adding above logic.


Both these approaches resulting in a blank cell.

Appreciate your attention and any information regd this.

Thanks & Regards
Raj