Year over Year calculations

[Eugene]

Hello, I am trying to  make a calculation in data module in order to take the YoY and MoM.

Can you provide me a simple way, step by step.
If it is more easier to active report you can give me instructions for it.

Thanks is advance.

[bus_pass_man]

The relative time current year and prior year items could be helpful.   

In this attachment the image on the right is a report using a month query item and the current year and prior year relative time measures.   The image on the left is a report which uses the parent measure and year and month, to compare the results.

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/t_ca_rel_date_create_module.html

This doc touches, along the way to its main objective, on the subject of modelling for relative time.

https://www-01.ibm.com/support/docview.wss?uid=ibm10884168&aid=1


In powerplay you could do year over year with more than just a pair of years.  It might be nice to be able to do that with a module.

[Eugene]

bus_pass_man, thanks a lot for your quick response.

I'd like to ask you a few more things in order to succeed to do the desired calculations in an active report.

First of all the report on the right  is produced from the report of the left? If yes , in which manner ?  Or  Are they 2 different reports ?

Can you be more precise.

Basically how can I fix the report on the right? I have to use the Fiscal Calendars ?

Thanks in advance.

[bus_pass_man]

First of all the report on the right  is produced from the report of the left? If yes , in which manner ?  Or  Are they 2 different reports ?

No, the report on the right is not produced from the report on the left.  They are two different reports.   They are from the same module.  The module is based on the sample gosldw data base.   The module has had relative time created in it.   I used the Gregorian calendar module.

The report on the left uses the sales fact quantity query item and the go time dim current year and month query items (I've in the module sorted month by month number ).  I included it so you could compare the values in it to the values in the report on the right, which uses the relative time measures.  I included current year so you could more clearly understand where the values were coming from.  I grouped current year to make the report a wee bit cleaner and thus clearer; that's all.

The report on the right, the report which uses relative time measures, uses the month query item and the current year and prior year relative time measures, which are among the measures which are automatically generated when you have set up relative time.

You will notice that the cell intersecting January and prior year has the value 1,604,216.   This is the value for January 2010 in the report on the left.  You will notice the cell intersecting January and current year has the value 2,047,167.  This the value for January 2011 in the report on the left.

There's no way to get around the requirement to do this modeling in the module.   

The stumbling blocks I encountered where:

• Getting my head around the concept of needing calculations in my fact tables to produce lookup references.
• Getting over my resentment about my beautiful data warehouse being despoiled with an inelegant method.
• Figuring out expressions to create the correct lookup values.
• Figuring out the sequence of actions to set up relative time.
• Figuring out how to use relative time in a report.  I was adding relative time measures AND time query items into my report and getting nulls.  It was only after some hard thought that I realized that a relative time measure is like a snail, which carries its house on its back, in that the relative time measure has the time dimension included with it.

Hope that helps.

[Eugene]

Thanks as lot for the reply.

[bus_pass_man]

Stumbled into this.  You can extend the relative time filters (which get used to create the relative time measures) to add stuff like the year before the prior year so you can do stuff like what you could do in powerplay.

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/t_custom_date_filters.html