Calculated field on multiple values filtering control

Question & Answer
,
1

apac country sales
Y SG 10
Y MY 11
N UK 12
N CN 13

Field “apac” having Y and N value, it is used a filtering control and allows multiple values.
when “apac” is filtered for Y&N, it should show UK, CN, APAC
when “apac” is filtered for N, it should show UK, CN
when “apac” is filtered for Y, it should show SG, MY

Tried using calculated field to check for values of filtering control. But there is error that “contains” does not accept multiple values and unable to convert multiple values using “join”.

any idea?

2

Hi @lbl
you would like to filter, right?
why not just using a filter for apac.
If you filter you table with apac (Y,N,ALL) you should achieve the result.
BR

1 Like
3

the challenge is to display different country name in different situation for Vertical Bar Chart.

  1. “apac” is filtered for Y&N, x-axis for country should be showing UK, CN, APAC (if non-apac & apac are selected, combine SG&MY as APAC)
  2. “apac” is filtered for N, x-axis for country should be showing UK, CN
  3. “apac” is filtered for Y, x-axis for country should be showing SG, MY (if only apac is selected)

any idea will be helpful.
Thanks!

1 Like
4

Hi @lbl

Please use the IN function instead of CONTAINS since you are dealing with a Parameter control that is list of strings.

Find below a case where the IN is used. You can adapt your case using this as reference.

Regards,
Giri

1 Like
5

paramAPAC is of String and Multiple values.

before applying any function, calculated field as “${paramAPAC}” is giving error - We encountered an error trying to save your calculated field. Please try creating it again"

“{paramAPAC}” is giving Field paramAPAC does not exist

6

Hi @lbl

Please share the screen shot wth the error message from QuickSight calculated field to take a look

7

@Giridhar.Prabhu

image
image459×553 12.4 KB

image
image892×613 11.2 KB

8

Hi @lbl

Since the parameter is a multi-value string list trying to just use it this way will results in an error.

However, if you evaluate your column with the parameter selections as follows it should work fine.

You can create your calculated field as follows. The NULL check is for the Select All case.

ifelse(IN({apac}, ${paramAPAC}), 1,
ifelse(IN(NULL, ${paramAPAC}), 1, 0)
)

In your visual filter the calculated field where the value = 1

1 Like
9

@Giridhar.Prabhu
Brilliant, it works! Thanks.

1 Like