Set Analysis Max completed Month current vs. previous year

[Corfiz]

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

[Daniel_Castella]

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

[Corfiz]

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. 

[Nagaraju_KCS]

May be this

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

or

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

[Daniel_Castella · Accepted solution]

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:

A:

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