Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
|
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;
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])
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
|
Try above or below function, below in screenshot of my function to Dec of Last year end for this fiscal year
Just pattern for your reference. maybe should try to calculate the position based on your starting date.
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;