Set Expression/Variable shorthand based on Parameters

mpeters1988 — Thu, 19 Jul 2018 09:57:02 GMT

So I've got a question which might be a bit of a stretch, but I want to ask you to bear with me:

I'd like to standardize some of my set expressions using a basic expression template based on variables and extending those expressions with parameters (which then might get filled with their own parameters).

The way I'm approaching this is by having a couple of basic set expressions contained in variables like so:

LET eSum.Sales = 'Sum({ $1 < $2 > }Sales)';

This can be defined in the load script but also in the front end, but this is just an example.

Now the first parameter is easy, this is my basic set definition, either a $ or 1, or whichever combination you prefer.
The second parameter is a bit more tricky. This could take in any value, but I would like it to work with a shorthand name for a set modifier.

1. So this could be a basic implementation:

$(eSum.Sales($,))
//Result: Sum({ $ <  > }Sales)

2. And this would be a more manual but advanced version:

$(eSum.Sales($, [Year]={'2018'}))
//Result: Sum({ $ < [Year]={'2018'}> }Sales)

But now I have a table loaded with some standard set modifiers:

SetShorthand	SetCode
ThisYear	[Year]={'$(=Max([Year]))'}
ThisWeek	[Week]={'$(=Max([Week]))'}

Now I can produce cases 1 and 2, but I would like to produce a variable which can concatenate a couple of set modifiers based on their shorthand. I have created a variable (see below) which can produce the string output of what I need, but I can't get it to work as a parameter of the first variable.

// Set modifier variable
LET SM = Replace(Concat({$<SetName={$1,$2,$3,$4,$5}>}DISTINCT SetDefinition, Chr(44)),Chr(35),Chr(36));
// Usage:
$(SM(ThisYear))
// Result:
[Year]={'$(=Max([Year]))'}

But when I try to use this variable as input for a parameter, I'm not getting the results I want. I've tried switching out the quotation signs, tried using the '=' sign for different variables and tried a couple of dollar expansions, but the best I can get is this:

// Usage: '$(eSum.Sales($,$(SM(ThisYear))}))'
Sum({$ <Replace(Concat({$<SetName={ThisYear>}[Sales])

Has anybody tried this kind of variable/set expression notations or does anybody know what I'm doing wrong?

Thanks in advance!

topic Set Expression/Variable shorthand based on Parameters in QlikView

Set Expression/Variable shorthand based on Parameters