Hi Kenji,
I suspect that you haven't created a FiscalYear column, rather that you are using the native calendar year, so your Max function is only returning the Max calendar year of 2022.
What @vikasmahajan is saying is that once you create a column in the script that is calculated from your date column and returns the fiscal year (eg: Starting in July), you'll be able to use the expression he has supplied above to return those values.
For example:
In your script you create a column using something like below (Assuming the fiscal year begins in July):
Data:
load
*,
if(month([Date]) < 7,
Year([Date])-1, Year([Date])) as FiscalYear
From YourDataFile.QVD;
This will return a numeric value. Using 28/02/2022 as an example would return: 21
Once the date goes past 01/07/2022 the FiscalYear will return: 22
Assuming you don't have any future dates in your data the Max function currently will return 21 for all records where [Date] is greater than 30/06/2021.
I hope this helps Kenji and that I have assumed your response correctly @vikasmahajan .
Thank you
Anthony
