Filtering only one measure by YTD

[James_Bonnell]

I have a cube-based report in Cognos 8.4.1 that has the following requirements:

Months of the Year, and a Yearly Total, for Measure1.  Measure2 and Measure3 need to be the full year - that's working fine.

However, Measure 1 needs to be only through the selected month based on a prompt, e.g. We need to show all 12 months, but I want the measure to be either the amount (if less than or equal to the selected period) or 0 (if not).

I'm almost but not quite there.

I can show the months of the year, and show the selected months, but can't quite see how to build a calculation or the like to have the measure be either the Amount or 0.

A slicer would fit the bill, but the slicer messes up the Measure2 and Measure3 requirements.

[CognosPaul]

Try using a tuple, tuple([YTD],[Measure1]) , in place of Measure1

[James_Bonnell]

Hmm. 

The YTD data item I came up with is:  periodsToDate([cubename].[Time].[Time].[FISCAL_YEAR],[SelectedPeriod])

doing tuple([YTDSet], amount) gives me this: Invalid parameter '[months_selected]' provided for 'tuple' at position '1' for 'dataItem="Data Item2"'.
This parameter is 'memberSet' type but is expected to be one of the following: 'member'.

(Okay, sure, it's a set expression, not a member).

I have these items to work with:

levels: Fiscal Year, Fiscal Period.

Selected Year defined as [FISCAL_YEAR] -> ?Fiscal Year?
Selected Period defined as [FISCAL_PERIOD] -> ?Period?
Months for Selected Year: children([Selected Year])
YTDSet: periodsToDate([cubename].[Time].[Time].[FISCAL_YEAR],[SelectedPeriod])

(set) Months_to_end_of_year: except([Months for Selected Year], [YTDSet])

(set) months_selected: except([Months for Selected Year], [months_to_end_of_year])

I'm doing something wrong, but I can't see what it is yet.

If I nest months_selected under Months for Year, I only get the months selected... and I need to show all 12 months.

[CognosPaul]

Okay, I was under the impression you had a YTD member coming from the cube.

Try doing

Code Select Expand

total([Measure1] within set periodsToDate([cubename].[Time].[Time].[FISCAL_YEAR],[SelectedPeriod]))

[James_Bonnell]

I figured it out!  (Inspiration comes at the oddest of times sometimes).

So.  The layout is now this:
                 Columns
                 YTDSet (data item)                                         Months-to-end-of-year (data item)    Expenses Total              Budget Year
ROWS         (mA or mB by account) - calculated measure       dummy measure - value 0                 
account1     measureA                                                      0                                                  measureA for year-end   measureA for Budget Year-end
account2     measureB                                                      0                                                  YTD Exp                       Budget for the full year

The keys were - put the YTD set (based on a Selected Period) and the Months-to-end-of-year on the same level, and add a dummy measure with value 0.

That way, there's always the correct measure for the months-to-end-of-year, which, in this case, is 0.  The expenses total correctly, and I didn't have to do a slicer which would have made the budgets not work right.

Thanks for the suggestions Paul but I figured it would be helpful to post my solution also.  I just needed to reshuffle the building blocks I already had - and add a dummy measure.