Set Analysis, Sum of Values with Max(Year) ignoring selections

[rcmartin]

Hello Community,

I am running into an wall trying to get a set analysis result that ignores selections and displays a sum of values in the current Fiscal Year.  After trying many results and solutions here in the community I must still be doing something wrong.  The field FiscalYear is formatted as Num() in the data load to avoid any date format problems.  The following expressions have been failures so far:

=SUM({<FiscalYear = {"=MAX({1}FiscalYear)"}>}Value)      selections change the result

=SUM({<FiscalYear = {"=1(MAX(FiscalYear))"}>}Value)     gives a result of zero

I have tried many combinations of single quotes, double quotes, {1}, and {$} as I have seen in other examples, all with no success.  Please help.

-Ryan

[Or]

SUM({1<FiscalYear = {"=MAX({1}FiscalYear)"}>}Value) or SUM(all {<FiscalYear = {"=MAX({1}FiscalYear)"}>}Value)

[rcmartin]

I appreciate the responses.  Unfortunately, they are not quite displaying the desired result of showing a result that ignores selections and displays a sum of values in the current Fiscal Year.

SUM({1<FiscalYear = {"=MAX({1}FiscalYear)"}>}Value)   is displaying a total for all records instead of current Fiscal year

SUM(all {<FiscalYear = {"=MAX({1}FiscalYear)"}>}Value)  is applying selections to the result

[rcmartin]

To further clarify, using the following INLINE table:

LOAD * INLINE [
ID, FiscalYear, Value
1,2019,1
2,2020,2
3,2021,3
4,2022,4
5,2022,5
];

I'm trying to construct an expression that provides the sum of records 4 and 5 for a value of 9 that ignores any selections made on app sheets.

[Or · Accepted solution]

Ah, apologies - I thought you'd already worked out your inner set analysis and were just looking for how to ignore selections. I hadn't noticed the inner set analysis is incorrect.

Try:

SUM({1<FiscalYear = {"$(=MAX({1}FiscalYear))"}>}Value)

Note that this will ignore all selections, not just the ones on FiscalYear, as I thought that was what you wanted to do. If you're looking to just ignore the selections on FiscalYear, remove the first 1 from the set analysis.

[rcmartin]

Beautiful man, beautiful.  Thanks a million!!!