topic Re: Set Analysis Max completed Month current vs. previous year in App Development

Set Analysis Max completed Month current vs. previous year

Corfiz — Wed, 26 Nov 2025 11:38:46 GMT

Hi,

I feel like a moron, but I can't figure it out myself.

I have a report that requires year selection. The final column is the last month of the selected year versus the same month in the previous year. For completed years, that would be "December selected year" vs. "December prev. year" - no problem.

But for the current year, we need the last completed month (NOT the current month!) vs. the same month in the previous year. I've set a flag for "MaxMonth" of each period, so that I can "set-analysis" that month for every year selected - but how do I get that same month in the previous year?

If I just set set analysis on the MaxMonth-Flag and take year-1, it will give me December, because that's the month with the MaxMonth-Flag in the previous year.

I've tried all kinds of things with "addyears()" in a set analysis function etc., but none of it worked. Help, please!

MaxMonthFlag SelectedYear Month vs.MonthPrevYear

1 2025 10 10-2024

1 2024 12 12-2023

1 2023 12 12-2022

Re: Set Analysis Max completed Month current vs. previous year

Daniel_Castella — Wed, 26 Nov 2025 12:02:48 GMT

Hi @Corfiz

Could you, please, try this set analysis?

=only({<MaxMonthFlag={1}>} Month) &'-'& num(SelectedYear-1)

I obtain the table below with it. However, I'm not sure if your data is more complex. In that case, if you could provide more details about it, I will try to help you:

Kind Regards

Daniel

Re: Set Analysis Max completed Month current vs. previous year

Corfiz — Wed, 26 Nov 2025 12:16:25 GMT

Hi Daniel,

thanks for your reply!

I need that expression to work in a set analysis-expression - something like

sum(

{< MonthNum = {"= addyears({< MaxMonthFlag={1} >} MonthYear, -1)"} >}

Sales )

or something to that effect.

Re: Set Analysis Max completed Month current vs. previous year

Nagaraju_KCS — Wed, 26 Nov 2025 13:57:01 GMT

May be this

Sum({<Year = { '$(=GetFieldSelections(Year) - 1)' },Month = {'$(=Only({<MaxMonthFlag={'1'}>} Month))'}>}Sales)

Sum({<Year = { '$(=Max(Year) - 1)' },Month = P(Month)>}Sales)

Re: Set Analysis Max completed Month current vs. previous year

Daniel_Castella — Wed, 26 Nov 2025 14:10:28 GMT

Hi @Corfiz

Then I think you will need to adjust a bit the backend. Let me explain:

First, I'm using the following example data.

Then, in the backend I apply this code:

LOAD

MaxMonthFlag,

'0' as PreviousYearFlag,

SelectedYear,

"Month",

Sales

FROM [lib://DataFiles/months.xlsx]

(ooxml, embedded labels, table is Sheet1);

MAP:

Mapping

Load SelectedYear&"Month",

'1' as Flag

Resident A

where MaxMonthFlag=1;

Concatenate (A)

LOAD

Applymap('MAP', (SelectedYear + 1)&Month, '-') as MaxMonthFlag,

'1' as PreviousYearFlag,

SelectedYear + 1 as SelectedYear,

"Month",

Sales

FROM [lib://DataFiles/months.xlsx]

(ooxml, embedded labels, table is Sheet1);

Finally in front end, a simple set analysis that you can see in the image below; together with the expected results:

I use a mapping to reassign the Flag to the proper month while I'm concatenating the previous year data to the current year data.

Let me know if it helps you.

Kind Regards

Daniel