Please Help Me with this code error when using 'group by' in script

[Solomoons]

Hi All,

The "invalid Expression" Error is popped out when I try to run the code below:

I want to do some operation in the 'if' function and try to use 'month(max(Fdate))' to get the biggest month shown in Fiscal year 23. But when I try to connect the strings, the error occurs. So I wonder what should I do to eliminate the error. Or could I use a variable in the code and replace the "Month(Max(FDate))" wit it? 

For your reference, the data and the final results I want would be like this:

 Any help it would be really appreciate!!

[SerhanKaraer]

Hello Solomoons,

The fields in the GROUP part by must match the fields in the LOAD part. We can not the see all the fields in your MDOT table, but it is sure that Fyear has to be stated in the GROUP part.

I hope it solves your problem.

[Solomoons]

Thank you so much! I have solved the problem😀

[Solomoons]

Hello SerhanKaraer,

After I loaded the data and drew a graph in sheet,  the dimension 'FY' shows like below:

Do you know why it looks like this way? And what should I do to make the dimension be "2019,2020,2021,2022,2023P3" ?

[SerhanKaraer]

Maybe you should omit FDate in GROUP BY part, just put Fyear. It seems that you calculate max in a more granular level than a year. If it does not solve , you must provide either data model or fields of MDOT table to be able to help me solid comments.

[Solomoons]

Hello,

I have tried to omit the 'FDate' field in GROUP BY part, but it occurs the 'Invalid Expression' error. 

And here is the whole script I have written, and I'd appreciate it if you could help me to find the problems and give me some comments~ Thanks!

[anat]

use only required fields in load statement ,

load fyear,fdate,if(.........) resident table group by fyear,fdate...

[Solomoons]

Hello Anat,

I have tried what you said, and make a test sample below, but result is still not what I want, and it seems the month(max(Fdate)) didn't work. 

the result is shown here: I want the FY to be (2022,2023P7) but it gives me (2022,2023P4, 2023P6, 2023P7)

I'd appreciate it if you could give me more comments