512329_en-US

liliconcom

Calculate average of x highest hourly values per day

We are looking to average the highest x hourly values in a given day across large data sets. For example, the average of the 12 highest hourly values within a given calendar day over a five-year period. The data include date, hour, and value for various metrics. Thank you for any suggestions.

AnalyticsData TableRe: Calculate average of x highest hourly values per day

There are many options for this:

1. Summary tables (and formulas)
2. Formulas with Col functions
3. Scripting

Attached is one option on how to do it with couple of formulas, first calculate rank of data for date (col number and col rank) and then based on that take top x (in example 3) and calculate average of those (col mean with if statement)

Re: Calculate average of x highest hourly values per day

Works great - any suggestions on how best to do it by "group" (or location, for example)? I have several sampling locations that have hourly values on the same date.

Re: Calculate average of x highest hourly values per day

You should be able to add more grouping variables to the Col functions

 

Re: Calculate average of x highest hourly values per day

Thanks for the help @jthi - don't know if I'm too novice but I can't seem to get it right.

 

Here is the formula that I'm attempting to group by "Site" in addition to date.

 

 

(1 + Col Number( 1, :Date )) - Col Rank( :"Gas"n, :Date )