Crosstab with Rollup for only some columns

[JGirl]

Experts, help please! 

I need to create the layout below.  I'm 90% there but stuck with the logic for the last column.

Code Select Expand


                          -----------------------------------------------------------------------------------------
                          |            |             | ... |             |      AVG        |       AVG            |
                          |WE 20080706 | WE 20080713 | ... | WE 20090607 | (all Wks YTD)   | (Only Last 10 Wks)   |
|------------------------------------------------------------------------------------------------------------------
|Monday | Total Sales     |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |   
|       | Number of Sales |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|       | Customer Count  |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|------------------------------------------------------------------------------------------------------------------
|Tuesday| Total Sales     |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|       | Number of Sales |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|       | Customer Count  |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|------------------------------------------------------------------------------------------------------------------
|...    | Total Sales     |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|       | Number of Sales |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|       | Customer Count  |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|------------------------------------------------------------------------------------------------------------------
|Total  | Total Sales     |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|       | Number of Sales |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|       | Customer Count  |       1234 |        1234 | ... |        1234 |            1234 |                 1234 |
|------------------------------------------------------------------------------------------------------------------


My report is a c8.4 crosstab (relational model), which has week ending date (all weeks in FYTD) as columns, and day of week with 3 nested measures as rows.

One of the aggregates is an average across all weeks in the report.  I have this in the report already, it works correctly, and it uses the statement:

Code Select Expand


average(currentMeasure within detail [Week Ending Date])


The other aggregate is an average across only the last 10 weeks (or less if its the start of the fin year).

I'm using a relational model, not dimensional and am struggling to get the logic for the 10 week average.  I've calculated a column called [Rolling 10 Week Flag] to flag the week as 'Y' if it needs to be included in the 10 week period and 'N' if it doesnt, but I dont know how to factor this into the aggreate statement.

I need something to tell the aggregate to only do a the selected periods, so I'm guessing I need something like the following:

Code Select Expand


average(currentMeasure within detail [Week Ending Date] for [Rolling 10 Week Flag])


Can anyone tell me what the correct syntax for the conditional aggregate is (bearing in mind that I have measures nested as rows)?

Thanks
J

[CognosPaul]

This is an idea I've been kicking around in my head, and I think it's applicable here.

In order to do this, you'll need to use traditionally dimensional functions. Try the following.

Create a query item directly above the [AVG (Only Last 10 Wks)].

Code Select Expand

total(currentmeasure within set filter ([Rolling 10 Week Flag],[Rolling 10 Week Flag]=1) )  You can set the box type to none to hide this on the report.

Next, change the [AVG (Only Last 10 Wks)] to

Code Select Expand

average(currentMeasure within set [Week Ending Date]) Change the detail to set to avoid this error:

Code Select Expand

Unable to build the aggregate dataItem="[AVG (Only Last 10 Wks)]". The referenced dataItem="Week Ending Date" was not found at the same nesting level.

There may be another way to do this using only relational functions, but I can't think of it at the moment.

[ibrusett]

How do you obtain the row totals?
I am not able to create a cross tab over relational data with totals on multiple measures...

[RubenvdLinden]

Change the detail to set to avoid this error:

Code Select Expand

Unable to build the aggregate dataItem="[AVG (Only Last 10 Wks)]". The referenced dataItem="Week Ending Date" was not found at the same nesting level.

Nothing to do with the original question, but I have been struggling with a similar error for ages!
Your suggestion solved my problem.

[blom0344]

A totally different approach is to solve this through a union with 3 sets:

set 1: type '1' ; define column as individual weeks
set 2: type '2' ; define column as 'all wks YTD'
set 3: type '3' ; define column as 'only last 10 wks'

set4: result of union 1-2-3

crosstab as:


                           type
                           column
day  | measure1
 
       | measure2

       | measure3

The type object automatically orders the columns in the crosstab if you assign values '1' / '2' / '3' to the subsequent sets.

You need to assign a random aggregate to the measure from set 4 , otherwise Cognos shows no data at all.

Obviously the individual queries need to have to proper filters to fetch the proper data.