keep string in dollar sign expansion expression

[FisherShi]

Hi, I'm working a expression to calculate the spend amount of last month, the last month is "2021-05".

And thus the expression i used is: sum({1<[Load Month] = {$(=maxstring([Load Month]))}>} [Total Cost Gross]).

I cannot get any result, it seems the return of $(=max([Load Month])) is 2016, the string "2021-05" is got calculated.

I'm thinking to change the data structure for the Load Month in script. I believe there's alternative way to fix it by adding some letter in the expression to keep the string not be calculated, anyone can help? thanks

Fisher

[MayilVahanan · Accepted solution]

Hi @FisherShi 

If load month is "2021-05" as string, you can make it as "Date" like below

=MakeDate(Left('2021-05', 4), Right('2021-05', 2)) gives 2021-05-01 

In Load script,  

Load *, MakeDate(Left(LoadMonth, 4), Right(LoadMonth, 2)) as LoadMonth from yoursource;

Then in front end,

Sum({1<[Load Month] = {'$(=Date(max([Load Month])))'}>} [Total Cost Gross])

[FisherShi]

it works, thank you Mayil.

Regards,

Fisher