Remove empty and NA from filters

Question & Answer
, ,
1

How to get rid of NA and empty strings from filters.
image
image

2

Hello @Samiksha

In Amazon QuickSight, you can filter out NA and empty strings by applying filters to your dataset or analysis. Here’s how you can do it:

Dataset Level

  1. Go to your dataset:
  • Open Amazon QuickSight and navigate to the dataset you are using.
  1. Create a calculated field to filter out NA and empty strings:

  • Click on Add and select Add Calculated Field.

  • Create a calculated field to replace NA and empty strings with a value you want to exclude. For example: ifelse({your_field} = ‘NA’ or {your_field} = ‘’, NULL, {your_field})

  • Name this calculated field appropriately.

  1. Apply filters in the analysis:
  • In your analysis, use the calculated field instead of the original field.
  • Add a filter on this new calculated field to exclude NULL values.

Analysis Level

  1. Open your analysis:
  • Go to the analysis where you want to apply the filter.
  1. Add a filter:
  • Click on Filter in the pane on the left side.
  • Select the field you want to filter.
  1. Configure the filter:
  • In the filter options, set the condition to exclude NA and empty strings. For example:
    • For NA:
      • Condition: is not equal to
      • Value: NA
    • For empty strings:
      • Condition: is not equal to
      • Value: ‘’
  • Apply the filter.

By following these steps, you can effectively filter out NA and empty strings from your dataset or analysis in Amazon QuickSight.

2 Likes
3

Thank You so much for this detailed explanation

1 Like
4

image
but then it shows like this I dont want it to be shown does not and NA over there. It is no more a dropdown now

5

One more thing I have created this field as a Calculated field and in the else part I have given it NA , because of that it is coming over there but I dont want to show over there can be possible ?

6

Hi @Samiksha

In the else part, you need to provide your field name. If the condition satisfies ‘NA’ or is blank, the value should be NULL; otherwise, it should be your field name.

Example: ifelse(**{your_field}** = 'NA' or **{your_field}** = '', NULL, **{your_field}**)

7

Actually, the problem is not solved yet I am getting Empty string in the filter control
image

I tried filtering out through doesnot contain Empty string and exclude blank but this filter is also not working

I also tried removing it in dataset through filter
image

I am assuming that it is because of the logic I have written as below :-

KPI TARGET = ifelse(

{BUSINESS SEGMENT} = “RLUS” AND {ITEM SCORE} >= 0.985, “Significantly Exceeds”,

{BUSINESS SEGMENT} = “RLUS” AND {ITEM SCORE} >= 0.955 AND {ITEM SCORE} < 0.985, “Exceeds”,

{BUSINESS SEGMENT} = “RLUS” AND {ITEM SCORE} >= 0.93 AND {ITEM SCORE} < 0.955 , “Meets”,

{BUSINESS SEGMENT} = “RLUS” AND {ITEM SCORE} >= 0.851 AND {ITEM SCORE} < 0.93, “Mostly Meets”,

{BUSINESS SEGMENT} = “RLUS” AND {ITEM SCORE} < 0.851, “Does Not Meet”,

{BUSINESS SEGMENT} = “FNWL” AND {ITEM SCORE} >= 0.985, “Significantly Exceeds”,

{BUSINESS SEGMENT} = “FNWL” AND {ITEM SCORE} >= 0.955 AND {ITEM SCORE} < 0.985, “Exceeds”,

{BUSINESS SEGMENT} = “FNWL” AND {ITEM SCORE} >= 0.93 AND {ITEM SCORE} < 0.955 , “Meets”,

{BUSINESS SEGMENT} = “FNWL” AND {ITEM SCORE} >= 0.851 AND {ITEM SCORE} < 0.93, “Mostly Meets”,

{BUSINESS SEGMENT} = “FNWL” AND {ITEM SCORE} < 0.851, “Does Not Meet”,

{BUSINESS SEGMENT} = “Genpact” AND {ITEM SCORE} >= 0.95, “Meets”,

{BUSINESS SEGMENT} = “Genpact” AND {ITEM SCORE} < 0.95, “Does Not Meet”,

“”

)
here in the else part I have given empty braces , might be because of that it is coming in the filter control but client dont want it to be visible in the filter control.

Actually it is very frustrating in Quicksight filters never work as expected . Can anyone help .

1 Like