COGNOiSe.com - The IBM Cognos Community

Hi Gurus,

I need to create a SPLY (Same Period Last Year) report.  The user selects the Month (January as an example) and the report would show sales data by Manager -> Sales Associate.  The data would show from January 2006 and January 2005.  I would have a column that would say ("Previous Year Pieces Sold") and another which would be ("Current Year Pieces Sold").  I could then have another column which would be a calculation to show +/-.
I have tried creating multiple tabular models but I can't seem to get them to join.  Also - I need to show customers for the Sales people even if they only had activity in 1 year.
I checked the tutorials that are up (they have been a great help) but I don't see anything like this.  Any help is appreciated!

Thanks,

Aaron

Here we go...

Create a TM called Sales

Within that TM create two Chid TMs (CurrentYear and LastYear)

CurrentYear TM (CY):
Select Manager, Sales Associate, CurrentYrPiecesSold where (filter) extract(year, [SalesTransDate]) = extract(year, {sysdate})  and extract(month, [SalesTransDate]) = extract(month, {sysdate})

LastYear TM (LY):
Select Manager, Sales Associate, PriorYrPiecesSold where (filter) extract(year, [SalesTransDate]) = extract(year, {sysdate} - 1) and extract(month, [SalesTransDate]) = extract(month, {sysdate})

Now add all the items from your child TM to your Sales TM:
Manager, Sales Associate, CurrentYrPiecesSold, PriorYrPiecesSold

Then add the following filter to the Sales TM:

Filter 1: SalesTM.Manager = CY.Manager and SalesTM.Sales Associate = CY.Sales Associate
Filter 2: SalesTM.Manager = PY.Manager and SalesTM.Sales Associate = PY.Sales Associate

Remember to set your aggregate to "Total".

HTH...Let me know if you need more clarification.

Thanks for the post!  I am still confused.  You mentioned in your post that I need to add all the items from my child TM to my sales TM.  I am confushed because when if I attempt to add the manager and sales info from the child TM's then it will append "1" to the end of the names.  On the report I need to show the Managers and Sales people and I now have 2 instances of each.  Also - you said that I need to set the aggregate to "Total".  Is it possible to "Total" a string field?  Am I misunderstanding what needs to get done?  Any advice is appreciated. 

Thanks again!

Aaron

Instead of adding them from the Model tab, click on the "Query Items" tab and add them from there.

You need to set the aggregate on the "CurrentYrPiecesSold" and "PriorYearPiecesSold".Ã,  These shouldn't be strings...I assume they're counts of some items.

I hope this clears it up for you.Ã,  Let me know if you have any other questions.

Hi--

Within my Query Items pane I see my 2 TM's (Sales Last Year & Sales Current Year).  Within each I have SalesPerson and Manager.  Putting in both versions creates the (1) after the name so I have 2 instances of each.  Am I creating the TM's incorrectly which is resulting in seeing both of them in the Query Items box?
I am attaching a screenshot of what my Query Items pane looks like.

Aaron

Select SalesPerson and Manager from only one of the TMs.Ã,  Then just add the Pieces sold from each TM (current and Last Year).

HTH

Aaron,
You can also grab the SalesPerson and Manager dimensions from your model and then bring in the measures (Pieces Sold Current and Last Year) from each Child TM.

This will work if you defined the relationships between the parent and children TMs in the filters (see above)

Thanks Aaron and HTH, your posts have enabled to create a similair Sales Current Year & Sales Last Year (+difference) report !

The only thing I need to implement now - I assume Aaron had the same requirement - is to list all sales from last year. With the current setup (as decribed by bdybldr) I only get the customers and their sales listed that appear in both years while there is a strong need to list the sales from last year when this year's sale is lower or even zero for a certain customer.

Can someone help me with this additional requirement ?

Thanks in advance.

Hi rel,
You can use a case statement to define CurrentYrPiecesSold and PriorYrPiecesSold so if no pieces were sold, it will evaluate to 0.

BTW, HTH = Hope this helps.  My name is Brent.  Welcome to Cognoise and thanks for your contribution.