topic Re: Help to convert Set Analysis to variable in App Development

Help to convert Set Analysis to variable

Thu, 20 Oct 2016 15:48:07 GMT

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

Re: Help to convert Set Analysis to variable

sunny_talwar — Thu, 20 Oct 2016 20:38:57 GMT

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), '#', '$');

Re: Help to convert Set Analysis to variable

jagan — Fri, 21 Oct 2016 08:16:43 GMT

Hi,

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

Regards,

jagan.

Re: Help to convert Set Analysis to variable

Aurelien_Martinez — Fri, 21 Oct 2016 13:33:36 GMT

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

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

Re: Help to convert Set Analysis to variable

Mon, 24 Oct 2016 22:56:15 GMT

Hi Aurelien,

I like this more logical "solution"

Many Thanks

Mike

Re: Help to convert Set Analysis to variable

Thu, 27 Oct 2016 12:09:40 GMT

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!