topic Re: Set Analysis, Sum of Values with Max(Year) ignoring selections in App Development

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

rcmartin — Wed, 15 Dec 2021 17:40:31 GMT

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

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

Or — Wed, 15 Dec 2021 18:15:30 GMT

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

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

rcmartin — Wed, 15 Dec 2021 19:00:40 GMT

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

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

rcmartin — Wed, 15 Dec 2021 21:14:08 GMT

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.

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

Or — Thu, 16 Dec 2021 09:28:32 GMT

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.

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

rcmartin — Thu, 16 Dec 2021 14:15:24 GMT

Beautiful man, beautiful. Thanks a million!!!