Time Dimension in Dynamic Reporting

[Former Member]

When creating a report that compares a current period (for example 2015.FEB) to the same period in the prior year (2014.FEB), what is the best logic to use in a report?  Do I use a local member formula to perform this?  I am willing to build in a property within the Time Dimension table if necessary.

I will probably have the same need to compare current period (2015.FEB) to prior period (2015.JAN) in the same report.

The average user isn't very knowledgeable working with reports besides picking from the context drop down so it needs to be where they pick the current month (2015.FEB) and the report does the rest.

***It should also be noted that we recently switched from a calendar year to fiscal year so it will not work to reference for example Period 2 in each year since 2014.FEB is period 2 and 2015.FEB is period 11 in each year respectively.

Thanks for the help.

Steve

[Shrikant_Jadhav · Accepted solution]

Hi Steven,

Kindly share some screenshot and structure of time dimension.

Shrikant

[Former Member · Accepted solution]

Shrikant,

See attached 4 screenshots of all of the properties that I have in the time dimension.

We only have one hierarchy for time.  As you can see its a little complex going from a calendar year reporting structure to a fiscal reporting structure. 2014 was a "short" year because of the change.

Thanks in advance,

Steve

[Shrikant_Jadhav · Accepted solution]

Hi Steven,

Suppose user is selectign 2014.JAN then waht will be the comparison ID ? is it 2013.JAN ?

Most of the case you can use EPMMemebrOffset formula.

Kindly share desired report format with selection and output result.

Shrikant

[Former Member · Accepted solution]

Yes you are correct 2014.JAN comparison would be 2013.JAN.  It would not make analytical sense to compare period 1 fiscal (2014.APR) to calendar period 1 (2014.JAN or 2013.JAN) so I am not going to worry about building that logic into a report.

This is what I am trying to make the report look like:

What would the local member formula be for "prior year"?

[Shrikant_Jadhav · Accepted solution]

Hi Steven,

Correct me if I am wrong, Your column structure is fixed and header like below

ACTUAL	Local M	ACTUAL	Local M	PLAN	Local M	FORECAST
2015.JAN	%	2014.JAN	%	2015.JAN	%	2015.JAN

If it is the case , use static reporting concept to meet your requirement,

Shrikant