Hello Cognos Gurus,
Requesting some expert advice for the following issue:
I have a crosstab for about 5 measures displayed by location. There are only two locations, which are the rows. (sample table shown at the bottom)
The columns are these Measures:
Quantity, Price, Duration_1, Duration_2, and TargetValue.
TargetValue is a custom calculation with static values based on the locations; my calculation is as shown below:
If (location = 'east') then (3.95) else if (location = 'west') then (4.25) else (4.10)The issue is when I am trying to create a summary row for both locations. I need the
total for Quantity and Price, the
average for Duration_1 and Duration_2 fields and for TargetValue I need the value
in the else clause (4.10), which is the average of both sites.
I tried to play around with Agg function and Rollup Agg function but having trouble with the summary for 4.10. I was hoping if there is a way to accomplish this without using multiple querries.
Here is a sample of how the crosstab should look like:
| Quantity | Price | Duration_1 | Duration_2 | TargetValue |
East | 10 | 501 | 4.33 | 2.99 | 3.95 |
West | 13 | 453 | 4.15 | 3.24 | 4.25 |
Summary | 23 | 954 | 4.24 | 3.12 | 4.10 |
Thank you for your help in advance.