Set Analysis - Year ago values

[odassier]

Hello all,

I've been stuck for days on a set analysis problem I hope you all can help me with.

I have a set of portfolio returns for each month for the past several years and want to use set analysis expressions to capture the portfolio performance at various dates based on user input (via filters).

For the latest date I've got:

=only({$<date={"=$(=max(date))"}>} [Active Return])

And that works fine.  If the user selects May 2017 for example, I get back the portfolio's annualized return as of that date.  Now for column two in this table I'd like to show the return one year prior to that selected date. 

using: =only({<$(=max(date)-365)}>} Ann_Returns)

doesn't work.  I may need a combined expression with $ for the max date (to take into consideration the user's selection for the date of the analysis) and a 1 for the year ago date so that the app does not think there is only one date in the data set (because of the selection).

Any help would be appreciated,

Olivier

[sunny_talwar]

May be this

=Only({<date = {"$(=Date(Max(date)-365, 'DateFieldFormatHere'))"}>} Ann_Returns)

[petter]

Your expression:

=only({<$(=max(date)-365)}>} Ann_Returns)

Lacks a left-hand-side fieldname - it have to be more like this:

=only({$<date={"$(=max(date)-365)"}>} Ann_Returns)

or

=only({$<date={"$(=AddMonths(max(date),-12))"}>} Ann_Returns)

[odassier]

Thanks for the help Petter,

I tried your expression but only get a "-" in return. 

Olivier

[sunny_talwar]

what is the format of your date field?

[odassier]

HI Sunny,

thanks for helping out.

I tried your expression:

=Only({<date = {"$(=Date(Max(date)-365, '[Date.autoCalendar.YearMonth]'))"}>} Ann_Returns)

But it returns the same value as for the max date in column 1.  It may be because the date selection is turning my data into a single point in time and we may need to add a modifier to the expression to make it 'ignore' that fact?

Olivier

[Anil_Babu_Samineni]

Are you sure, Do you have value Ann_Returns for Max date?

[odassier]

mm/dd/yyyy so 05/31/2017 for example.

Olivier

[sunny_talwar]

I wanted you to put the format within the single quotes, for instance if date is format as MM/DD/YYYY, then this

=Only({<date = {"$(=Date(Max(date)-365, 'MM/DD/YYYY'))"}>} Ann_Returns)

[odassier]

Sorry, my bad.

Y tried with MM/DD/YYY in the expression and still get the same value as Max data in columns 1

Olivier