Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 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 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))
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
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 🙂
@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))
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?
@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))