Geomean based on distinct values in another column

[mikeh_cbre]

I have a table which contains a range of prices with corresponding year, and want to calculate the geometric mean by distinct year.

At present, I'd do this in SQL using the following query:

select distinct [Year], exp(avg(log([Price]))) as [Geomean]
from [Table]
group by [Year]

Is there a straight-forward way to replicate in Alteryx, perhaps using a custom expression with Summarize tool (if that's actually possible)?

Ideally, any solution should be flexible in terms of the grouped values (e.g. multiple values, spatial references etc.). My immediate thought is to use a SQL query in the input and control parameters using action tools. However, it'd be incredibly useful to crunch the numbers mid-workflow.

Cheers,

Mike

[nick_ceneviva · Accepted solution]

Could you test if the following gets you matching results to your SQL query:

1. Formula tool that takes the log of the price: Log(Price).  You could either create a new column or update the existing price column.
2. Summarize tool that then groups by the year and takes the average of the Log(Price)
3. Formula tool that updates the average price to be equal to Exp(Avg_Price)

I think that should match what you are getting from SQL but definitely test to make sure!  Unfortunately it doesn't look like there is a way to create a custom formula in the summarize tool.

[JoeS · Accepted solution]

I believe the above is the correct method to create it.

I have attached a workflow on a sample that does just that, and confirmed the results against your SQL script too.

[mikeh_cbre]

Thanks, Nick. Far simpler than I'd envisaged! Hadn't even occurred to me to break the expression down in that way...

Much obliged.

[mikeh_cbre]

Cheers, Joe. Works a treat.