Difference calculation across dimensions in crosstab report

[godaniel]

I have a report that provides sales data for two different years.  Years is a level in the dimension.  If using sales as the measure in a crosstab, how do I calculate the difference between the sales measure across the two dimensions?

Example:

	2007		2008
	Sales	Qty	Sales	Qty	Sales Diff
Store 1	$420,000	7,421	$426,000	7,522	$6,000
Store 2	$340,000	6,253	$312,000	6,347	($28,000)

In addition, how would I also get the % difference?

I think there is probably an easy answer for this, but I don't know it.

[rockytopmark]

Calculated Member

[2008] - [2007]

[godaniel]

Thanks for the reply, but your post does not provide enough detail to proceed.  You can't simply make a calculated member with [2008] - [2007] as the expression definition otherwise I would have been done a long, long time ago.

[2008] and [2007] are two different members of the dimension.  I've come up with some weird way of doing it, but don't consider it the right way:
total(case when [Year] = 2008 then [Sales] else -[Sales] end)

I don't like that way, so is there a better way to do this?

[rockytopmark]

godaniel -

You most certainly do the calculation as easily as I have written... done it... doing it.  If this is not acceptable, then there are usually other options...

You may want to define more generic members such as Current Year, Prior year, etc, so that specific membership is not used in the calculation.  These are  usually automatically available in PowerCubes, and can be created in DMR.

An important function to know... Tuple(). Using Tuple() will allow you to compare the currentMember/measure to another intersection for getting your %.

Best of luck, RTM

Attached a report spec that demos this... uses the GO Data Warehouse package.

[rockytopmark]

You can also use dimensional functions such as parallelPeriod() prevMember() etc... to do these without specifying particular members.

[godaniel]

Putting "[2008] - [2007]" into the expression gives an error: Expression Parsing Error.  The query contains a reference to at least one object '[2007]' that does not exist.

Maybe I am missing something entirely in how the crosstab works.  Sales and Qty are both Facts.  Years and Stores are both levels in dimensions.  Years contains two members: 2007 and 2008.  Stores also contains two members: Store 1 and Store 2.  So let's say Years is a level in the dimension, Business Days.  How would I get the difference between the Sales facts for 2007 and 2008?  The last post implies 2007 and 2008 are both Facts, which they aren't.  If I had "2007 Sales" and "2008 Sales", then I could simply do [2008 Sales] - [2007 Sales] and be done with it.

The documentation for using tuple is quite sparse and not much use.  Any links to useful documentation would be appreciated.

[godaniel]

Using your suggestion I see what I was doing differently.  I was using <#Years#>, not <#2007#> and <#2008#> specifically.  Once I dragged the actual 2007 and 2008 members into the crosstab I was able to use [2008] - [2007] as you mentioned.

Is there a way to do this using the generic <#Years#> level rather than hard coding the report to 2007 and 2008?

Thank you very much!

[cognos_guru]

I've been having the same problem when doing calculations in a crosstab.  Since you are trying to do the calculation based off of a specific value and not a dimension, it is tricky.  I think it is possible to use something like total(YEAR {at YEAR=2008}) - total(YEAR {at YEAR=2007}) to get isolate those values, but I haven't been able to find enough documentation on the syntax to make it work...

Let me know if you find anything

[CognosPaul]

If you always have exactly two members in your set, you could do something like: item([YearSet],0) - item([YearSet],1).

If your set has n number of members, you could then use the head or tail functions:
FirstTwo: head([YearSet],2)
item([FirstTwo],0)-item([FirstTwo],1)

LastTwo: Tail([YearSet],2)
item([LastTwo],0)-item([LastTwo],1)

[godaniel]

The <Years> level includes years back to 2002.  The report is filtered by having the user pick the two years they'd like to compare.  Is it possible to write something like the following:

item(<Years>, ?Year2?) - item(<Years>, ?Year1?)

I'm sorry if I have strayed from my initial question, but this is what I'm trying to deal with.

[CognosPaul]

How are you prompting the user for the year? If the user is selecting the actual MUN, then you should be able to do ?Year1? - ?Year2?. I'm afraid there's no way of determining the position of a specific member within a set, at least none that I've found.

Are you using an actual filter? If you're referencing the members themselves in the crosstab, or list, then you don't need to filter or slice.

Try this:
1. create a new crosstab.
2. Put stores in the rows.
3. Put a query item into the column: #prompt('Year1','memberuniquename','dim.hier.currentyear')#
4. Put another query item in next to it: #prompt('Year2','memberuniquename','dim.hier.previousyear')#
5. Put your measures in under each item. This will create the crossjoin effect.
6. In the final column create this item: tuple(#prompt('Year1','memberuniquename','dim.hier.currentyear')#,salesmeasure) - tuple(#prompt('Year2','memberuniquename','dim.hier.previousyear')#,salesmeasure). If the salesmeasure is the default measure then you don't even need the tuple.