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!
