Sum WHEN max date - help!!

SAM81 — Mon, 29 Mar 2021 10:42:23 GMT

I am trying to create an expression that generates a SUMPRODUCT value but only for the latest date in my data set.

Current expression is SUM(CLOSED*C_AGE)/SUM(CLOSED). But this will calculate for all dates in my data set.

I need this to only calculate for the latest week in my data set based on the week field name being 'WEEK_COMMENCING'

Can anyone help?

Re: Sum WHEN max date - help!!

GaryGiles — Mon, 29 Mar 2021 11:02:35 GMT

Something like this:

=sum({<WEEK_COMMENCING={'$(=max(WEEK_COMMENCING))'}>} CLOSED*C_AGE) / sum({<WEEK_COMMENCING={'$(=max(WEEK_COMMENCING))'}>} CLOSED)