Variable in Paramaterized Set Analysis Expression

[bc5891]

Hi

I am having difficulty in the following expression and grateful for any help:

let vMetric = SUM({<DATA_SOURCE = {$1}, METRIC_CODE = {''$'&'(=(MaxString($2)))''}>}AMOUNT)

the expression in the front end would be:

vMetric(DB1,METRIC_CODE_1)

So, $2 would equal a selection from a field selected in the front end (e.g. METRIC_CODE_1).  When I have tried to follow other threads on this I haven't come across maxstring being used for a varying field selection.  Has anyone any ideas?

Reason I do this is there could be different filters (via different data islands) selected on different sheets to show different metrics.

The non-paramterized expression works like so:

SUM({< DATA_SOURCE = {'DB1'}, METRIC_CODE = {'$(=$(vMetric1))'} >}AMOUNT)

where vMetric1 = Maxstring(METRIC_CODE_1); When the user then selects a specific metric from metric_code_1 the expression updates.

Sample data load would be like this:

DATA:
LOAD * inline
[
AMOUNT,DATA_SOURCE, METRIC_CODE
200,DB1,A,
1000,DB1 B
200,DB1,C,
1000,DB2,D
];

METRIC_CODE_1:
LOAD * inline
[
METRIC_CODE_1
A
B
];

METRIC_CODE_2:
LOAD * inline
[
METRIC_CODE_2
C
D
];

keen to hear any ideas. thanks

[marcus_sommer · Accepted solution]

I would say maxstring() is used to ensure that the expression always returned a value. If you remove it and using just $2 the expression will only work if there is a single value selected within the metric-field. As far as there are more values selected or none the result would be NULL because it would be the same like only([metric-field]).

- Marcus

[bc5891]

Thanks for the reply Marcus but I do not think that will work.
e.g. vMetric(DB1, METRIC_CODE_1) 

The above expression should

SUM Amount where DataSource = DB1, but also where METRIC_CODE = field selection in METRIC_CODE_1 (a data island)

I need what the field value selection of METRIC_CODE_1 is, METRIC_CODE_1 is not the actual value selection.

So, I want to be able to change it in another sheet to: vMetric(DB1,METRIC_CODE_2) which then gives me the value when a single selection in field METRIC_CODE_2 is made, if that makes sense?

EDIT - I re-read your message and get you understood what I meant 🙂

[Kushal_Chawda · Accepted solution]

@bc5891  below works.

Create a variable vMetric on front end instead of in script

Put below expression in variable (Without "=")

SUM({<DATA_SOURCE = {$1}, METRIC_CODE = {"$(=MaxString($2))"}>}AMOUNT)

Then below expression chart works

=$(vMetric("DB1",METRIC_CODE_1))

[bc5891]

Hi Kush (and Marcus)

Thanks to both...

Both methods work ....... if the variable is created in the frontend - do you know of any workaround to have this in the backend as I like to keep all variables in a specific file?

[Kushal_Chawda · Accepted solution]

@bc5891  I would advise to use front end variable itself because it will keep the expression as it is without manipulating it. Otherwise , if you use it in script, you may need to manipulate your expression like below to add the "$" within set analysis like below

let vMetric = 'SUM({<DATA_SOURCE = {$1}, METRIC_CODE = {"' &chr(36)&'(=maxstring($2))"}>}AMOUNT)';

Then you can use the same expression

=$(vMetric("DB1",METRIC_CODE_1))