Simple way to do relative measures Sales CY vs PY

[gpollock]

All,

It has been too long since I've worked on a cube, and I'm trying to figure out how to do something that I could easily do with relational.

I'm working in Cognos 10.2.2 on a dynamic cube in an active report.  I have a crosstab for measures.  In the rows, I have some measures: Net sales, margin, etc; and in the columns, I have relative time: WTD, MTD, QTD, YTD.  Using a cube, the relative time is already figured out, so it's easy.

The problem is that the request calls for a row that's Net Sales % Growth, which is Net Sales CY / Net Sales PY, that is calculated for each of the columns--in the WTD column it's sales WTD / sales WTD PY etc.  In a relational package, I would create a data item for each measure to get the relative time, and then a calculation to compare.  I'm trying to put this together in a cube.

See a crude example below:

	WTD	MTD	QTD
Sales	4	8	10
Sales Growth over LY	2%	3%	2.5%
Margin	2	4	5

Is this something I would use a tuple for?  I'm having to get back into the research on cubes, and want to figure out what's the simplest method to achieve this.

I appreciate the help

[gpollock]

All, just following up in case anyone else has this issue..  We are going to try to create a custom dimension to represent Year-over-year.

Here's something I tried with parallelPeriod.  Our time dimension is [Calendar].[Year > Quarter > Month > Week > Day] and Year is the year level.  Below hard-coded doesn't return anything, much less using currentMember.
parallelPeriod ([Sales and Operation Metrics].[Calendar].[Year > Quarter > Month > Week > Day].[Year],1,[Year to Date (2016)])

and, this is what I tried for the successful value of sales (before trying to do the % comparison):
tuple(parallelPeriod([Sales and Operation Metrics].[Calendar].[Year > Quarter > Month > Week > Day].[Year],1,currentMember([Sales and Operation Metrics].[Calendar].[Year > Quarter > Month > Week > Day])),[Net Sales])

Our next solution is to create a YoY dimension, that does not include years.  The hierarchy is Quarter->Fiscal Month->Fiscal Week->Day of Year (1-365).  This way, the user can pull QTD which is weeks 27-33, and then tuple/nest current year or prior year to get the comparison.  We're a retail chain, and our business cares more about a given period last year than a given period vs its prior period.

[cognostechie]

I worked for a retail chain too in Texas which used to sell golf products and I made a cube for them to show YOY (Months, quarters and weeks). They did not have dynamic cubes so you must be working for somebody else. From what you posted, it is difficult to understand what exactly is that you need. First you said that you want WTD. YTD and measure the sales and growth which is pretty standard when using a cube. Then you said you want to use the relational technique. Now you say you need YOY which is different than WTD, MTD. In fact, if you have custom fiscal calendar then WTD would not be calendar WTD but week 27 compared with week 27 of last year which is what retail chains want.

A clear post gets more replies than a confusing post because people would not like to spend time on figuring out what you need.

[gpollock]

For clarification, I wanted a way to produce the table above using a dynamic cube via crosstab.  The crosstab would have relative time for WTD, MTD, QTD, and YTD (I abbreviated it for simplicity) and measures would include sales values and percent growth.  Percent growth is defined as sales for a given period vs that same period in the prior year.  So for the August column, the % growth would be August 2016 vs August 2015 as defined in our date dimension.  The WTD % growth value would be week XX 2016 vs week XX 2015; and so on.  I don't want to add the extra columns for these time periods prior year, so I wanted the percent growth value to be as an extra row, per the visual.  If you would like more clarification on this, please let me know.

I want to use a dynamic cube as the data source for this, but this is a process that could easily be done in a relational package via IF-THEN-ELSE values.  Unfortunately, the remaining requirements and drilling capabilities require a cube.  I thought using parallelPeriod or a sibling function would help me accomplish the prior year period, but I keep getting blanks.  I'm probably doing something wrong on it, or missing something.

Now to clarify my last post, this is to say what I'm doing to accomplish the goal, in case someone else searches for help.  We're creating a custom dimension that will give the YOY comparisons.  It won't be the same as a true time dimension because year is not considered in the hierarchy; Quarter 3->August will have the dates for August in every year; the week level for week XX will have the days in every years' week XX; and we'll use tuples/filters to pull out current year, prior year, etc.

I'm not certain this is the best way to do it; if there are better ways I'm open. 

[cognostechie]

If you build your cube based on your time dimension ( Ex: August 2016 could be  1st Aug to 2nd Sep 2016   whereas  August 2015 could be 3rd aug 2015 to 31st aug 2015) then this should be a straight and simple report with drag and drop without using any tuples. The cube automatically creates % as well as amount growth.
However, what is this report going to give you. You are not measuring the sales by any dimension so this would give you company wide sales which makes it very simple for analysis.

Yes, the WTD sales as well as growth would be current week (lets say wk 40 vs wk 40 of previous year regardless of which dates those week span) and this would be also straight if you build the cube by specifying the 4-4-5, 4-5-4 or whatever calendar you use.

YOY dimension should be created when you want to see 12 months of sales compared to 12 months of last year all on the same screen, not for this purpose.

[gpollock]

I am very interested in how to do this as you describe, and I have to be missing something.  Horizontal Axis year relative time: WTD, MTD, etc.  Vertical Axis is the measures: Net Sales CY, (Net Sales CY vs PY %).

[cognostechie]

Go thru the user guide for Transformer and Dynamic Cubes. Pay attention to 'Lunar Calendar'. It lets you specify how to build your custom calendar in the cube. There is some difference in both the tools as to which date it will resolve to when retrieving 'Same Day Last Year' or 'Same Week Last Year'. In your case, both the tools will do the job as both will let you do the settings accordingly.

Horizontal and Vertical axis have nothing to do with OLAP. You are thinking in relational terms and then trying to imagine the solution. It's a whole lot better to build the cube right so that the users can have a self-service BI which would work not just for your current requirement but will also let them create hundreds of reports more without having to ask you to create every single report for them. With a relational model, you would spend time on creating custom calculations and achieve only one report with a specified format.