Variance between 2 crosstabs (On Dimensional Source)

[iBoy]

Hi everyone, thanks for the help with my previous question about sorting members in the opposite direction of the cube layout ("RESOLVED - Ordering Members within a descendant function") - Glad to say we got it sorted, check it out if you have a similar issue!

A question that I've seen raised a few times, but never seen a fully logical answer is....

Creating a cross tab that shows the difference between 2 sets of data

My problem example is as follows;

Both individual crosstabs are matching and structured like so -


                        Year
Income
-Web Sales
-Store Sales
-Mail Sales
Expense
-etc
-etc
-etc

Year is defined by a prompt ?pYear?.  And the [Year] item shows a set of members, which contains the current and succesive years based on its calculation lastPeriods (-5,?pYear?)

The two crosstabs are defined by the member chosen for each from a dimension called "Version".  2 prompts exist to capture these, ?pVersion1? and ?pVersion2?.  Version1 is assigned to the column (and hidden) in crosstab 1, and Version2 is assigned to column (and hidden) in crosstab 2.

The 2 crosstabs use the same query - this approach has been taken to try to keep performance high

How would I go about creating a 3rd crosstab, that will show excactly the same format to the previous two but calculate the difference between each year of the two - which contain different values by the version they report on?

Can a data item using the function variance () be used?  Or must I look into something bigger?

I'm fairly silly at this aspect of reporting, so feel free to dumb and extend you answers to benefit me and any others who will read this in future!


Regards,
iBoy

[blom0344]

I just used a cube to define 2 near-identical queries to be used within a union-set. The trick is to define the measure of the second query as  -1*[some_measure], so the rollup (using total) effectively nets the variance. Beforehand I doubted this would work at all, and the generated MDX is a little hard to fathom.
The union set only works if the referenced measure is added to the query:
Query1:
Year
Date
Measure
Measure1 (copy of measure)

Query2:
Year
Date
Calculated Measure  (as  -1*[Measure])
Measure

Union set projected:

Year
Date
Measure (total as aggregate setting)

[CognosPaul]

I'm assuming that your crosstab looks something like:

Corner | Var 1
       | Years
-------+-------
Rows   |

In your third, replace that hidden Ver1 node with

Code Select Expand

variance(currentMeasure within set set([Variance 1],[Variance 2]))

Also, are you sure your users are expecting variance? Generally I find standard deviations or simple differences to be easier for users to understand.

[blom0344]

Variance in terms of statistical measure would not make a lot of sense with just 2 input values. I think the OP wants arithmetic differences   ;)