[SOLVED] Crosstab + Averages

[kc9400]

Ok,

I have a cross-tab which displays the number of users within certain locations over an 11 month period.

I need to return the average in another column for 10months, for 6months, and for 3months prior to the current month.

I will attach screen shots in the hopes this will help explain what I am trying to achieve.

(The attached screen shot is taken from an excel example of what I am trying to achieve)

[blom0344]

It is not that hard, but it requires some insight into the use of 'sets'   If you are a little familiar with SQL , then you will know that it is possible to  unify 'sets' of data by the use of  union / union all.

Cognos actually delivers this in a similar fashion by letting you union different sets (with their own filters etc.) by building a new query from individual similarly structured detail queries.

In your case define 3 queries [10months, for 6months, and for 3months]  that have the same structure , adding a data item to each query as a column header:

in SQL terms:

select '10 months' as header,.........................   from  ....................
union all
select '6 months' as header,.........................   from  ....................
union all
select '3 months' as header,.........................   from  ....................

[kc9400]

Cheers for that blom, helped a lot.