Selecting multiple dates using an expression, within an expression

[mattphillip]

Good afternoon,

I'm hoping someone might be able to assist. We have a sheet within one of our apps where as a sheet action we automatically select a number of corresponding dates to populate a multi-year line chart.

At present we specify the dates within an expression using the following code: 

='(15/01/2019|14/01/2020|12/01/2021|11/01/2022|10/01/2023)'

However, we'd like to make things a little more automated. As such I've tried drafting a more advanced expression. The below script produces the same result (in a text box), however, when it's been tried in the sheet action, it doesn't make any selections.

=(max({1} Date)&'|'&date(max({1} Date-364),'DD/MM/YYYY')&'|'&date(max({1} Date-728),'DD/MM/YYYY')&'|'&date(max({1} Date-1092),'DD/MM/YYYY')&'|'&date(max({1} Date-1456),'DD/MM/YYYY'))

I tried it in a variable, vMultiYear as ='$(vMultiYear)' but still have no luck.

I would be most appreciative it someone could take a look and let me know where I'm going wrong.

Thanks,

Matt

[maxgro · Accepted solution]

Try this (change the date format M/D/YYYY in my expression)

=chr(39) & '('
& max({1} Date) &'|'& min({1} Date) &'|'& date(max({1} Date-364),'M/D/YYYY') &'|'& date(max({1} Date-728),'M/D/YYYY')
& ')' & chr(39)

[mattphillip]

Brilliant thanks Maxgro!!