crosstabs

[mprof]

hi,

I have created a crosstab with years as rows departments as main columns and (number of men, number of women) as sub columns.
and have a total row that summarize the data for the 10 years.
what i want to do is to compare it to the average of previous 10 years .
so i have created a new query that calculate it for me,
when i'm trying to put the query result (query2) in the cross tab(which is query1) it shows up as empty.
i have tried to create a "big" query that calculates the averages and put it in the total row, and again nothing.

please assist,

Michael

[wyconian]

Hi

First question is are you using a relational or dimensional/cube package?

If you're using a relational package I would do this;
You need a query that calculates the averages for the same columns you have in the main query.
Then  join the queries together on the common fields.
The crosstab should reference the join query. 
You should then be able to pick up the averages in the same way you would any other measure.

If you're uisng a dimensional package or a cube the easiest thing to do would be to add a new measure into the package/cube which calculates the averages.  Then you can just use the average measure in the same way you use any others.

Hope that helps.

[mprof]

thanks for the reply,

i have combined the 2 queries together into one big query (which is almost the same).
the problem is that i have a total lines (avgs, difference etc.)
and i cannot use calculations with objects that are not in the crosstab.
is i want to put in a cell the following calculation: avg(A)-avg(B) then first i must create a data item that calculates this and then i must add this data item into the crosstab columns. (or create layout calculation and then i need to add the avg to the columns).
i just want to add the final calculation without additional rows/columns.
is it feasible?

thanks again

[wyconian]

Hi

Sorry I'm not really sure what you mean.  You should be able to have a calculation on the xtab which references items in the query but which aren't on the xtab.  What issues are you having with that?