Max Day of Max-1 Week of Max Year

[Anonymous]

Tried this but doesn't work:

=Sum({1<Date={"=Max({1<Week={'$(=Max({1<Year={'$(=max(ALL Year))'}>} Week)-1)'}>} Date)"}>} Value)

It works up to the point to get max-1 week:

=Sum({1<Week={"$(=Max({1<Year={'$(=max(ALL Year))'}>} Week)-1)"}>} Value)

But fails when I try to get to max Date.

[sunny_talwar]

Max returns a number and now date. You might need to convert it to date Date(Max(.... and need a dollar sign expansion at the beginning

=Sum({1<Date={"$(=Date(Max({1<Week={'$(=Max({1<Year={'$(=max(ALL Year))'}>} Week)-1)'}>} Date))"}>} Value)

Not sure of the parenthesis, but you get the idea

[Anonymous]

I had already tried dollar sign expansion but didn't mention earlier ... tried Date() but no result:

=Sum({1<Date={"$(=Date(Max({1<Week={'$(=Max({1<Year={'$(=max(ALL Year))'}>} Week)-1)'}>} Date)))"}>} Value)

if I just use this I get desired date result:

=Max({<Week={"$(=Max({1<Year={'$(=max(ALL Year))'}>} Week)-1)"}>} Date)

[sunny_talwar]

May be you need to add the date format for your field Date???

=Sum({1<Date={"$(=Date(Max({1<Week={'$(=Max({1<Year={'$(=max(ALL Year))'}>} Week)-1)'}>} Date)), 'YourDateFieldFormatHere')"}>} Value)

because if those two formats differ, you won't get any result.

Best,

Sunny

[Anonymous · Accepted solution]

Actually, the original expression was correct (with $ expansion) but I needed to add in extra filter to get last Friday, else, it was giving a Sunday with no data. Thank you Sunny for responding.

Since, Date field was coming from Calendar, no need to add Date(), one that worked for me is:

=Sum({1<Date={"$(=Max({1<Week={'$(=Max({1<Year={'$(=max(ALL Year))'}>} Week)-1)'}, Region={'Americas'}>} Date))"}>} Value)