Logical ifelse with overlapping condition

Question & Answer
,
1

Hi all, I am struggling with creating calculated field for my filter. The result range I want is

  1. 0 to 32

  2. above 33

  3. above 100

  4. less 100

This is my code…

Range = ifelse(sum < 100, ifelse(sum >= 0 AND sum < 33, ‘0 to 32’, ‘above 30’),
‘above 100’
)

How to solve this use case?

2

Hello @aho, welcome to the QuickSight Community! What kind of an error did you get with that calculated field above? I think if that ifelse statement didn’t work you can probably do it without nesting multiple statements. I’ll type out a possible solution but you may need to change it a little because I am not certain how you want to handle less than 100 or above 33.

ifelse(
sum >= 0 AND sum < 33, '0 to 32',
sum >= 33 AND sum < 66, 'above 33',
sum >= 66 AND sum < 100, 'less than 100',
'above 100'
)

You could also add a final else for above 100 like sum >= 100, 'above 100', NULL and then just filter your visual by that calculated field to exclude nulls. I think the first option would work though so let me know if that helps!

3

Hello @DylanM I didn’t get any error. I just wasn’t able to filter less 100. What function to do overlapping condition in quicksight?

4

Hello @aho, I think I understand now. What I would say the solution would be is create a string parameter for the grouping names. You will need to set a default value so you can utilize it in a calculated field. Then create a control, maybe a dropdown, and input each of the options you listed above. After each string value enter onto a new line to set another option.

Then, in you calculated field, instead of trying to return your string values, return the sum values you want to see. It will look like this:

ifelse(
sum >= 0 AND sum < 33 AND ${Param} =  '0 to 32', {sum}
sum >= 33 AND ${Param} = 'above 33', {sum}
sum < 100 AND ${Param} = 'less than 100', {sum},
sum >= 100 AND ${Param} = 'above 100', {sum},
NULL
)

Then to make sure the table filters appropriately, you can add a filter to the visual for the calculated field I wrote above. Use a custom filter, set it to Does not equal, enter an impossible value, and then select exclude nulls in the dropdown. Now it should work as you expected!

5

@DylanM thank you so much. That solution is amazingly work. I can implement to 2 problems using that 1 solution. Kudos for you. Have a good one!

1 Like