Max of Values across Dates

[cfountain72]

Hello,

I am trying to generate avg of maximum Ages and avg of minimum Ages by Dates. So in the example below, the bottom row of Avg (17.4 and 87) would be the values I want to return.

Date       Count Min      Max

7-Aug	25	30	85
6-Aug	28	7	98
5-Aug	33	22	78
4-Aug	25	6	90
3-Aug	21	30	75
2-Aug	30	4	94
1-Aug	23	23	89
Avg		17.4	87

I could create a summary by date table in the load script (as above), but that would remove the flexibility of filtering at more granular levels. I tried using aggr and the expression below, but I get no results. Is it a syntax issue, or something more fundamental?

avg(distinct Aggr(Max({<FirstIPOPEncAdmissionTime = {"$(='>=' & timestamp(now(0)-100))"}>} AgeYears), date(FirstIPOPEncAdmissionTime, 'MM-DD-YYYY')))

Thanks in advance for your help!

Chris

[jbhappysocks]

Hi. Try removing the date() around FirstIPOPEncAdmissionTime. And I also don't think you want to use distinct.

So try

avg(Aggr(Max({<FirstIPOPEncAdmissionTime = {"$(='>=' & timestamp(now(0)-100))"}>} AgeYears), FirstIPOPEncAdmissionTime))

(You probably need to add a new field floor(FirstIPOPEncAdmissionTime) as FirstIPOPEncAdmissionDate in the script and use instead of Time)