Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bc5891
Contributor III
Contributor III

Variable in Paramaterized Set Analysis Expression

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

Labels (2)
3 Solutions

Accepted Solutions
marcus_sommer

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

View solution in original post

Kushal_Chawda

@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))

View solution in original post

Kushal_Chawda

@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))

 

View solution in original post

5 Replies
marcus_sommer

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
Contributor III
Contributor III
Author

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

@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
Contributor III
Contributor III
Author

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

@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))