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

Question & Answer
,
2

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
image

Step 2 - Create a parameter “SelectedAttrs”

image
image603×567 23.9 KB

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

image
image1244×875 57.2 KB

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:
2022-07-14_11-39-42 (1)

4 Likes