Discussion

1
Replies
484
Views
Otto_Perdeck Member since 2010 9 posts
PEGA
Posted: 4 years ago
Last activity: 4 years 6 months ago
Closed

What is the preferred way to create a weighted average in reports?

Hi,

What is the best way to create a weighted average in a report definition? The aggregation functions include Sum, Min, Max, Average but there is no weight field. 

Imagine I have data like this

ITEMPRICEQUANTITY
A1025
 5100

I want to have the average price weighted by quantity, per item.

The average price is 7.5, the weighted average should be (10*25 + 5*100)/(25 + 100) = 6.

In SQL, this would be something like

SELECT ITEM, SUM(QUANTITY*PRICE)/SUM(QUANTITY)
  FROM <TABLE>
GROUP BY ITEM

How do I accomplish that best in a report def?

Thanks

Otto

 

 

Reporting
Moderation Team has archived post
Share this page LinkedIn