3 year unit sum average for the selected 'character string' fiscal year: without creating additional value

[nezuko_kamado]

How can I draw a bar chart by quarters(qtr) of the average of the selected FY, previous FY and two previous FY?The actual data combined with multiple large data sets, and I need to keep the fiscal year format as original (character) to avoid errors. Because FY is string value, I failed to select the previous, and 2year previous FY using -1 or -2. 

Instead of changing FY to numeric, first, I add the table assigning the order of fiscal year(FY). Because I need to keep FY value as it is. (I know how to convert it as numeric, but I'm looking for a way to pick previous two years with keeping it as string)

FYsort:
Load*inline [
FY, SortOrder
FY19, 1
FY20, 2
FY21, 3
FY22, 4
FY23, 5
FY24, 6
FY25, 7
]

but still not able to grab previous FYs.

The below is the sample table you can use

Table:
Load*inline [
FY, Qtr, Unit
FY19, 1, 10
FY19, 2, 30
FY19, 3, 25
FY19, 4, 40
FY20, 1, 35
FY20, 2, 25
FY20, 3, 30
FY20, 4, 50
FY21, 1, 20
FY21, 2, 15
FY21, 3, 50
FY21, 4, 45
FY22, 1, 30
FY22, 2, 25
FY22, 3, 25
FY22, 4, 50
FY23, 1, 40
FY23, 2, 25
FY23, 3, 40
FY23, 4, 10
FY24, 1, 20
FY24, 2, 43
FY24, 3, 50
FY24, 4, 10
FY25, 1, 30
FY25, 2, 45
FY25, 3, 25
FY25, 4, 50
];

[MatheusC]

@nezuko_kamado 
Try extracting the final numbers from your FY field

Num(SubField(FY, 'FY', -1)) as Finalyear.

and then in the application you can use the max() function


- Matheus

[nezuko_kamado]

Thank you, I know this could be an option too but I'm looking for the way without creating an value.

Because my app has 10 tables with large sets, and there should be a reason this FY should stay as character. If I create a new value of Final year, there's no room for another filter for the new value. 

[Kushal_Chawda · Accepted solution]

@nezuko_kamado  try something like below for max(FY)-1

=sum({<FY ={"FY$(=max(keepchar(FY,'0123456789'))-1)"}>}Unit)