Last Year Data, and filtered data on data range applied to slicer in DMR

[Kyne]

Hello, Gurus.

I'm having a problem of getting last year data from dimensional data.

i've successfully  done with the ranged data using filter, startRange, endRange as below;

I made data items of startRange, end Range, and filter
and drag filter to slicer in query.
and it works correctly for all measures in the report. e.g. for  [Sales Cube].[Measures].[Value]

=> filter
intersect(lastPeriods(-9999,[StartRange]),lastPeriods(9999,[EndRange]))

=> StartRange

#'[Sales Cube].[Period].[Financial Year].[Day]->:[M8].[[Period]].[Financial Year]].[Day]].&' +sb(timestampmask(prompt('StartDate','date',$current_timestamp,'','','T00:00:00Z'),
'yyyy-mm-dd')+'T00:00:00') +']]'#

=> EndRange

#'[Sales Cube].[Period].[Financial Year].[Day]->:[M8].[[Period]].[Financial Year]].[Day]].&' +sb(timestampmask(prompt('EndDate','date',$current_timestamp,'','','T00:00:00Z'),
'yyyy-mm-dd')+'T00:00:00') +']]'#

but, my client additionally wants to add last year measure for this report.

e.g. if I select the range between '2015-01-01' and '2015-01-31'
last year data for [Sales Cube].[Measures].[Value] would be ranged '2014-01-01' and '2014-01-31'.

i've been trying to use tuple or MDX function for query calculation, but i've got confused the structure.

guide me if you have any ideas for this requirement.

thanks in advance.

[cognos810]

Hello Echo7117,
I think the below steps should meet your needs.
1) Create a data item [StartRange-1] which will take you an year back from the selected start date, using
parallelPeriod ([NAME_OF_YOUR_YEAR_LEVEL],1,[StartRange]). So if you selected '2015-01-01' the result would be '2014-01-01'.

2) Create a data item [EndRange-1] which will take you an year back from the selected end date, using
parallelPeriod ([NAME_OF_YOUR_YEAR_LEVEL],1,[EndRange]). So if you selected '2015-12-31' the result would be '2014-12-31'.

3) Create a data item like filter, call it filter1 lets say.
intersect(lastPeriods(-9999,[StartRange-1]),lastPeriods(9999,[EndRange-1]))

4)Create a Set expression, lets call it [UnionOfTimeRanges]
Union([filter],[filter1])

5) Delete the current slicer and add [UnionOfTimeRanges] in the slicer area.

6) Finally, get your numbers:
Measure value for Current selection, lets call it [Measure Value TY]:
AGGREGATE( [Sales Cube].[Measures].[Value] within set [filter])
And, Measure Value for Prior Year selection, lets call it [Measure value LY]
AGGREGATE( [Sales Cube].[Measures].[Value] within set [filter1]).

Hope it helps,
Cognos810

[Kyne]

Hi cognos810,

you broadened my horizon and made my day.  :)

Thank you.

Hello Echo7117,
I think the below steps should meet your needs.
1) Create a data item [StartRange-1] which will take you an year back from the selected start date, using
parallelPeriod ([NAME_OF_YOUR_YEAR_LEVEL],1,[StartRange]). So if you selected '2015-01-01' the result would be '2014-01-01'.

2) Create a data item [EndRange-1] which will take you an year back from the selected end date, using
parallelPeriod ([NAME_OF_YOUR_YEAR_LEVEL],1,[EndRange]). So if you selected '2015-12-31' the result would be '2014-12-31'.

3) Create a data item like filter, call it filter1 lets say.
intersect(lastPeriods(-9999,[StartRange-1]),lastPeriods(9999,[EndRange-1]))

4)Create a Set expression, lets call it [UnionOfTimeRanges]
Union([filter],[filter1])

5) Delete the current slicer and add [UnionOfTimeRanges] in the slicer area.

6) Finally, get your numbers:
Measure value for Current selection, lets call it [Measure Value TY]:
AGGREGATE( [Sales Cube].[Measures].[Value] within set [filter])
And, Measure Value for Prior Year selection, lets call it [Measure value LY]
AGGREGATE( [Sales Cube].[Measures].[Value] within set [filter1]).

Hope it helps,
Cognos810

[Lynn]

Hi cognos810,

you broadened my horizon and made my day.  :)

Thank you.

And this, ladies and gentleman, is an outstanding example of how to say thank you! I applaud both cognos810 and echo7117.
;D