Help to convert Set Analysis to variable

[#1]

Hi

I have the following  code which works fine within Qliksence:

Sum({<SaleDate={"$(=Date(Max(SaleDate),'YYYY-MM'))-*"},SaleFiscalYear=,SaleYear=, SaleMonth=>} Sales)

As this is used a number of times and subject to change I would like to store it as a variable within the script. However is  fails to return anything. Can someone help the the formatting please. I think the single v double quotes and Chr(36) $ is the culprit here.

Many Thanks

[sunny_talwar · Accepted solution]

You can try like this:

LET vVar = 'Sum({<SaleDate={"$' & '(=Date(Max(SaleDate),''YYYY-MM''))-*"},SaleFiscalYear=,SaleYear=, SaleMonth=>} Sales)';

Note the two single quotes around the date formatting -> ''YYYY-MM''

Or you can try this:

SET vVar = Sum({<SaleDate={"#(=Date(Max(SaleDate),'YYYY-MM'))-*"},SaleFiscalYear=,SaleYear=, SaleMonth=>} Sales);

LET vVar1 = Replace($(vVar), '#', '$');

[jagan]

Hi,

I think the issue is with the -* here,  why you are using -* in this expression.

Regards,

jagan.

[Aurelien_Martinez]

SET vVar = Sum({<SaleDate={"#(=Date(Max(SaleDate),'YYYY-MM'))-*"},SaleFiscalYear=,SaleYear=, SaleMonth=>} Sales);

LET vVar1 = Replace('$(vVar)', '#', '$');

[#5]

Hi Aurelien,

I like this more logical "solution"

Many Thanks

Mike

[#6]

Is there a way to write this calculation into script:

=Sum($(vSetMTD) IsSale)

Note the vSetMTD is itself a concatenated string as follows:

Let vSetMTD = '{$<MonthID = {' & Chr(36) & '(=Max(MonthID))},' & Chr(10) &

                'SaleDate = {"<=' & Chr(36) & '(=Max(SaleDate))"},' & Chr(10) &

                'Year = ,' & Chr(10) &

                'Quarter = ,' & Chr(10) &

                'Period = ,' & Chr(10) &

                '[Period (#)] = ,' & Chr(10) &

                'Month = ,' & Chr(10) &

                'StatusKey-={12} >}';

which holds the MTD set. Works fine is I use the inline code above but would rather create a variable at the script level for ease of maintenance.

Many Thanks!