I want a comma separated value in a qliksense text box.
Consider the following data
REGION|MARKET|METRIC|VALUE
ASP|INDIA|S1|1
ASP|CHINA|S1|0
ASP|INDIA|S2|0
ASP|CHINA|S2|1
ASP|HONGK|S1|1
EUR|UK|S1|1
EUR|UK|S2|0
USA|TX|S1|1
USA|TX|S3|1
I want an output in a Qlik Sense text box with the following logic:
Sum of the VALUE
column where VALUE=1
for metric S1.
Desired output in text box: ASP(2), EUR(1), USA(1)
I would really appreciate your help.
You can use this statement:
Concat(Aggr(REGION & '(' & Sum({<METRIC={'S1'}>} VALUE) & ')', REGION), ',')
Which will return you the desired result:
In case VALUE
is not binary you can add VALUE = {1}
to the set expression like:
Concat(Aggr(REGION & '(' & Sum({<METRIC={'S1'}, VALUE = {1}>} VALUE) & ')', REGION), ',')
Statement to load data:
Data:
Load *
Inline [
REGION,MARKET,METRIC,VALUE
ASP,INDIA,S1,1
ASP,CHINA,S1,0
ASP,INDIA,S2,0
ASP,CHINA,S2,1
ASP,HONGK,S1,1
EUR,UK,S1,1
EUR,UK,S2,0
USA,TX,S1,1
USA,TX,S3,1
];