topic keep string in dollar sign expansion expression in QlikView

keep string in dollar sign expansion expression

FisherShi — Wed, 02 Jun 2021 03:15:07 GMT

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

Re: keep string in dollar sign expansion expression

MayilVahanan — Wed, 02 Jun 2021 04:14:17 GMT

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])

Re: keep string in dollar sign expansion expression

FisherShi — Wed, 02 Jun 2021 05:06:25 GMT

it works, thank you Mayil.

Regards,

Fisher