Max of Values across Dates

cfountain72 — Mon, 16 Aug 2021 12:45:10 GMT

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

Re: Max of Values across Dates

jbhappysocks — Tue, 17 Aug 2021 14:53:05 GMT

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)

topic Re: Max of Values across Dates in App Development

Max of Values across Dates

Re: Max of Values across Dates