If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Year to Date Member Set/Level based on User Selected Month

Started by bberryhill, 09 Mar 2017 11:51:31 AM

Previous topic - Next topic

bberryhill

My report is built on a transformer cube in which I have let transformer create the date dimension.  I am getting different results with a calculated measure when I nest the transformer created YTD level and my calculated YTD set.
My measure is, for example, Revenue / Qty.
My report should contain the Measure with YTD and Prior YTD nested under the measure.

I started with a data item YTD_Months_SelectedMonth: periodsToDate([Sales Analysis].[Date].[Date].[Year], [selectedMonth])
That data item correctly returns the set of month members.  When the data item is added to the report it creates a column for each month.  I want a single column. 
So I created a data item YTD_Avg: average (currentMeasure within set [YTD_Months_SelectedMonth] )
That data item does what it says - it averages the currentMeasure but the currentMeasure is calculated for each month in the set and then averaged - which is the wrong answer.
If I use the YTD level that transformer created I get the correct result, that is the total revenue for YTD / total quantity for YTD.

I have determined that I can create individual data items that calculate the total ... within set ... and get the correct results.
total ([Revenue] within set ( [PYTD_Months] ) ) / total ( [Quantity] within set ( [PYTD_Months] ) )

I created a Query Calculation as a Member Set using the Dimension Date and the Hierarchy YTD   ... looks very much like the transformer created YTD level but the member set returns a column for each month.

How can I create a calculated "something" that behaves the YTD level?

Thanks for your help.