Weighted average in subtotals (crosstab)

[rteruyas]

Hi guys,
I'm a little stuck with one report that uses relational data.

I have a crosstab report with 2 measures: qty and price.
I want to show the weighted average under summary section for Price.
For some reason I cant figure out yet, it only works when the column "Weighted Average" is added to the report as a 3rd measure

If I try to remove it, it the summary section shows the same value regarding what date is displayed.
I've tried adding the field as a property for the summary section, for the row, for the column, but none of them have worked yet.

Any idea if I'm missing anything. I'll try to recreate this issue with sample data.
Thanks in advance!

[Cognos_Jan2017]

Your Weighted Average is a "trusted calculation" as part of your query?

What if you unlock the Total Row for each day, and drag in the Weighted Avg
Calculation (which should respond to each day)?

I don't use Crosstabs much, and perhaps trying to Cut the
Weighted Avg Column will result in a Cognos objection.

If so, make a new Crosstab with only the Qty and Price Columns.  Then unlock
the Totals Row for the Day, and drag in your Weighted Avg calculation.

Let us know if that works.

HTH, Bob

[Pratap Reddy]

Hi guys,
I'm a little stuck with one report that uses relational data.

I have a crosstab report with 2 measures: qty and price.
I want to show the weighted average under summary section for Price.
For some reason I cant figure out yet, it only works when the column "Weighted Average" is added to the report as a 3rd measure

If I try to remove it, it the summary section shows the same value regarding what date is displayed.
I've tried adding the field as a property for the summary section, for the row, for the column, but none of them have worked yet.

Any idea if I'm missing anything. I'll try to recreate this issue with sample data.
Thanks in advance!

Hi,

The code may help you.

If you want to add calculation field instead of Avg.Productline and Avg.Year.you can unlock and remove the Avg.Productline or Avg.Year and use your Calculation field Weighted Average.

Regards,
Pratap

[rteruyas]

First of all, thanks @Cognos_Jan2017, @Pratap Reddy for your ideas and sorry for the late response.
I finally had some time to build the report using sample data: you can see it attached as WeightedAverage.txt

If you open the attachment, Page4 of the report is what I'm trying to achieve.
However, the weighted average in the summary section doesn't change for product line unless Weighted Average is displayed in the report (as in Page3)

Workaround:
The workaround is Page5 of the report
- For the measure displayed as a column I set "Define contents = Yes"
- Changed padding to 0
- Output format = singleXLS (office 2002) to be able to change the column width.
However, I'm still trying to figure out if there is a better way to achieve this


Any idea why Page4 is not working.
Thanks in advance again

[Cognos_Jan2017]

Can you send the Report formatted as Excel (If it requires 2002 format ... If not, please send
as Excel 2007) to me?

My email address is listed on my Cognoise info.

Thank you, Bob