Geomean based on distinct values in another column
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Could you test if the following gets you matching results to your SQL query:
- Formula tool that takes the log of the price: Log(Price). You could either create a new column or update the existing price column.
- Summarize tool that then groups by the year and takes the average of the Log(Price)
- 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks, Nick. Far simpler than I'd envisaged! Hadn't even occurred to me to break the expression down in that way...
Much obliged.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Cheers, Joe. Works a treat.
