Set expression to display previous year December values (with if statement inside set expression)

[andewan]

I want to create a Column 'Equity Previous Year December' to display Equity for the Previous year's December values against the PERIOD_YEAR_MONTH column ranging from 2023-01 to 2024-03

PERIOD_YEAR_MONTH	Equity current period	Equity Previous Year December
2024-03 2024-02 2024-01 2023-12 2023-11 -- -- 2023-01 2022-12	324234 755456 342555 657574 2131821 -- -- 485325 723944	657574 657574 657574 723944 723944 -- -- 723944

I tried the set expression below but only the first part of if condition gets evaluated. How can I achieve my goal? 😕 

=Sum({< 

     PERIOD_YEAR = {"$(=If(PERIOD_YEAR = '2024', '2023', '2022'))"}, 

     PERIOD = { '12' }

    >}

Total Equity current period)

[BrunPierre]

Hi, it is much easier to create a flag within the script as such:

If(Year(Date#(PERIOD_YEAR_MONTH, 'YYYY-MM')) >= Year(Today()) - 3
and Month(Date#(PERIOD_YEAR_MONTH, 'YYYY-MM')) = 12, 1, 0) as Last3YrsDec

And then use this simplified expression

Sum({<Last3YrsDec = {1}>} [Total Equity current period])

[andewan]

Thank you for your reposnse, but it did not work.

With this code, I get the following test column which is different from my requirement. 

PERIOD_YEAR_MONTH	Equity current period	Test column
2024-03 2024-02 2024-01 2023-12 2023-11 -- -- 2023-01 2022-12	324234 755456 342555 657574 2131821 -- -- 485325 723944	0 0 0 657574 0 -- -- 0 723944

[WangKun]

Try above or below function, below in screenshot of my function to Dec of Last year end for this fiscal year

[WangKun]

Just pattern for your reference. maybe should try to calculate the position based on your starting date.

[andewan · Accepted solution]

Thanks, @WangKun 

In principle I did something similar but in the back-end because I will need the 'Equity Previous Year December' calculation at several places. Here is how created the column in the back-end:

T1:
Load * Inline [
PERIOD_YEAR_MONTH,	PERIOD_YEAR, PERIOD, PreviousYear_Dec, Equity_current_period
2024-03, 2024, 03, 2023-12, 324234
2024-02, 2024, 02, 2023-12, 755456
2024-01, 2024, 01, 2023-12, 342555
2023-12, 2023, 12, 2022-12, 657574
2023-11, 2023, 11, 2022-12, 2131821
2023-01, 2023, 01, 2022-12, 485325
2022-12, 2022, 12, 2021-12, 723944
];

T1_only_december_values:
Load PERIOD_YEAR_MONTH As PreviousYear_Dec,
	Equity_current_period As Equity_current_period_Previous
Resident Test
Where PERIOD = 12
;

Left Join(T1)
Load PreviousYear_Dec,
    Equity_current_period_Previous
Resident T1_only_december_values
;

Drop Table T1_only_december_values;​