Experts, help please!
I need to create the layout below. I'm 90% there but stuck with the logic for the last column.
-----------------------------------------------------------------------------------------
| | | ... | | 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:
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:
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
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)].
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 average(currentMeasure within set [Week Ending Date]) Change the detail to set to avoid this error: 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.
How do you obtain the row totals?
I am not able to create a cross tab over relational data with totals on multiple measures...
Quote from: PaulM on 14 Jun 2009 02:20:29 AM
Change the detail to set to avoid this error: 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.
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.