Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Help to convert Set Analysis to variable

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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Help to convert Set Analysis to variable

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

5 Replies
MVP
MVP

Re: Help to convert Set Analysis to variable

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

MVP
MVP

Re: Help to convert Set Analysis to variable

Hi,

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

Regards,

jagan.

amartinez35
Valued Contributor

Re: Help to convert Set Analysis to variable

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

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

Not applicable

Re: Help to convert Set Analysis to variable

Hi Aurelien,

I like this more logical "solution"

Many Thanks

Mike

Not applicable

Re: Help to convert Set Analysis to variable

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!

Community Browser