How to use values from a list aggregated field in a table to a filter in Quicksight?

[Santosh]

I have a dashboard in Quick-sight where a table is being used which contains a field that has values list aggregated.(Please refer the attached image).

I need to create a filter in a dashboard that will have a dropdown with distinct values from the list aggregated values so that if i select/check a particular value from a dropdown then it should filter out the list aggregated record by matching the string i selected in a drop down.

I tried to use the split function to get the distinct values from the list aggregated values from the field. However, the filter did not work as i expected.

Please suggest possible solution. Will appreciate any suggestions.

[robdhondt]

Hi @Santosh -

You can accomplish this by building off a multi-select workaround by darcoli found here

In Your Main DataSet

Step 1 - Create a dataset that 1 row for every unique attribute name and add to your analysis

Step 2 - Create a parameter “SelectedAttrs”

Step 3 - Create a calculated field “c_selected_attrs”.

// if attr is located in attr parameter then remove it (unselected)
// if attr is not located in attr parameter then append to parameter (selected)
ifelse(
    locate(${SelectedAttrs}, attr) > 0,
    replace(${SelectedAttrs}, concat(attr, ','), ''),
    concat(${SelectedAttrs}, attr, ',')
)

Step 4 - Create a calculated field “c_selected_flg”. This will be used to place selected attr at the top of the table.

ifelse(locate(${SelectedAttrs}, attr) > 0 , 1, 0)

Step 5 - create a calculated field “c_selected_order”. This is used as custom sort to sort by selected then alphabetically

rank([{c_selected_flg} DESC, attr ASC],[],PRE_AGG)

Step 6 - Create table visuals for your filter list and your results. Add conditional formatting for your c_selected_flg to act as the highlighter

In your Attr List DataSet

**Step 1 - Create a calculated field “c_selected_cnt”. This uses difference in string length to tell how many attr are selected **

strlen(${SelectedAttrs})-strlen(replace(${SelectedAttrs},',',''))-1

Step 2 – Create a calculated field “c_selected_filter_flg”. This will be used to filter your result table.

// hard coded 10 matches
// count delimited list of selected attrs, flag true if matches = selected attr cnt
ifelse(
ifelse(locate(concat(' ', {agg_list}),split(${SelectedAttrs},',',2))>1,1,0)+
ifelse(locate(concat(' ', {agg_list}),split(${SelectedAttrs},',',3))>1,1,0)+
ifelse(locate(concat(' ', {agg_list}),split(${SelectedAttrs},',',4))>1,1,0)+
ifelse(locate(concat(' ', {agg_list}),split(${SelectedAttrs},',',5))>1,1,0)+
ifelse(locate(concat(' ', {agg_list}),split(${SelectedAttrs},',',6))>1,1,0)+
ifelse(locate(concat(' ', {agg_list}),split(${SelectedAttrs},',',7))>1,1,0)+
ifelse(locate(concat(' ', {agg_list}),split(${SelectedAttrs},',',8))>1,1,0)+
ifelse(locate(concat(' ', {agg_list}),split(${SelectedAttrs},',',9))>1,1,0)+
ifelse(locate(concat(' ', {agg_list}),split(${SelectedAttrs},',',10))>1,1,0)+
ifelse(locate(concat(' ', {agg_list}),split(${SelectedAttrs},',',11))>1,1,0)
={c_selected_cnt},1,0)

Result:

[Santosh]

I will give it a try and update you. Thanks for your suggestions. Highly appreciated.

[Santosh]

It worked thankyou so much…

[albertocalle]

I love this solution!
I am looking for something similar: a “concatOver”.
I mean, having:
PRODUCT_ID || COMPANY_ID
1 || A
2 || A
3 || B
4 || C
5 || C
6 || C

And I want to get, in a pivot table grouped by company_id, something like this
COMPANY_ID || PRODUCT_ID
A || ‘1, 2’
B || ‘3’
C || ‘4, 5, 6’

Thanks!

[kmontgomery]

@robdhondt I’m not able to implement your solution.

I think I am missing something at the step where the parameter is set as the values in c_selected_attrs do not look correct. The parameter configuration matches exactly what was in your Step2 screenshot.

I worked your solution until the end and am also not able to get my other data source (Diagnosis is analogous to your agg_list, I think) to update at all based on selections from the dataset with every unique attribute name per row (Step 1).

The selected value should filter out all results except row 3 in “Results to filter”:

Thanks for the clarification.