8.4 - Cube Source - Relative Time

[kado]

Cognos Experts  ???,

I have a Time Dimension that is giving me 'Month Closed' (most recent Financial Closed month typically a month or 2 prior from the Current Month). I have a requirement to build a dynamic report based on this 'Month Closed' that will give me all the months in the Current Year and all the months in the Prior Year.

I was thinking I could use some of the family functions but I am getting Errors when trying to compare the 'Month Closed' to the full Time dimension (which has about 5 years of data) which are in 2 different hierarchies. So 2 questions:

1) What would be a good approach for extracting the 'Month Closed' from this hierachy so as to compare it to the other TIME hierarchy? Perhaps by creating a variable and then placing it into a data item?

2) Once I can align the 2 hierarchies what would be a good combination of family functions to get all the months from the previous year as well as the current year?

THANKS,
kado

[MFGF]

Hi,

Having the Month Closed in a different hierarchy from the rest of the Time Dimension complicates things horribly!

What are the rules for identifying the Month Closed in the main Time hierarchy?  If you can define a query calculation utilising these rules to identify the Month Closed member, you could then use the lastPeriods() function or PeriodsToDate() (or something similar) to retrieve the preceding months.

Regards,

MF.

[kado]

Month Closed is set by a FLAG from our Source Table. So it is typically 1 or 2 (sometimes 3) months in the past.

How could I use a query calculation in conjunction with my cube to isolate this field within the time hierarchy (so as to take advantage of the family functions you mentioned)?

I wonder if I could convince my audience to have it based on the current date (similar to this technique http://www.cognoise.com/community/index.php/topic,1834.0.html) and just warn them.

THANKS for the feedback!!!!!

[MFGF]

Hi,

Is the flag being brought through as an attribute of the Month level?  If so, you could use a filter() function - filter ([your month level],[your flag attribute] = 'the flag value') - to determine the current month from the main time dimension.

MF.

[kado]

Unfortunately not. Perhaps I could get it on the docket.

Thanks for the feedback!