Set Analysis - Average of one field where another field is equal to monthly maximum

bchastai — Thu, 23 Dec 2021 18:28:44 GMT

I have sensor data that takes readings of temperature at different depths every day. I want to have a table that lists the average temperature each month for only readings taken at the *maximum* depth for that month.

Example input data:

ID	Date	Depth	Temperature
1	1/1/2009	4	50
1	1/1/2009	5	40
1	1/15/2009	5	38
1	2/2/2009	1	60
1	2/2/2009	6	40

Desired output:

Date	Avg Temp at Deepest
Jan-2009	39
Feb-2009	40

I've been trying every set expression I can think of, but it's not giving me the correct results.

I tried Avg({<Depth={"$(=Max(Depth))"}>}Temperature), but that just returned null for each month, *unless* I selected a specific month, and then it would give me the correct value (but just for that month).

I tried Avg({<ID=P({<Depth={"$(=Max(Depth))"}>})>} Temperature), but that gave me values that were just wrong.

I even tried some Avg(Aggr(Avg(... expressions, but couldn't get them to work either.

Any ideas?

Re: Set Analysis - Average of one field where another field is equal to monthly maximum

lironbaram — Thu, 07 Nov 2019 18:34:06 GMT

try this expression

FirstSortedValue(aggr(avg(Temprature),Depth,Month),-aggr(Depth,Depth,Month))

where month is a field i created in the script by using this expression : monthstart(Date)

topic Re: Set Analysis - Average of one field where another field is equal to monthly maximum in App Development

Set Analysis - Average of one field where another field is equal to monthly maximum

Re: Set Analysis - Average of one field where another field is equal to monthly maximum