If else statement with multiple or conditions satisfied

Question & Answer
, ,
1

Hi, I have an ifelse statement in Quicksight where I want to attribute the same field to multiple conditions and have all of them be true:

ifelse(
{apn_geo} = 'ALL' OR {apn_geo} = 'APJ', 'APJ',
{apn_geo} = 'ALL' OR {apn_geo} = 'EMEA' , 'EMEA',
{apn_geo} = 'ALL' OR {apn_geo} = 'NAMER', 'NAMER',
{apn_geo} = 'ALL' OR {apn_geo} = 'LATAM', 'LATAM',
{apn_geo} = 'ALL' OR {apn_geo} = 'GCR', 'GCR','NULL')

My formula is attributing all the values that have “ALL” in the apn_geo value to “APJ” because that is the first condition that is in the formula and when I select “NAMER” or any of the other geos the “ALL” values are not being counted. Is there a way to have the values with “ALL” appear no matter which apn_geo you select?

2

Hi @grazhang

Welcome to the QuickSight community!

ifelse function evaluates conditions sequentially, and the first true condition is applied. To ensure that the {apn_geo} = ALL values are counted for all regions, you can restructure your formula to include logic that considers both the specific region and ALL.

Example:

ifelse(
  {apn_geo} = 'APJ' OR {apn_geo} = 'ALL', 'APJ',
  {apn_geo} = 'EMEA' OR {apn_geo} = 'ALL', 'EMEA',
  {apn_geo} = 'NAMER' OR {apn_geo} = 'ALL', 'NAMER',
  {apn_geo} = 'LATAM' OR {apn_geo} = 'ALL', 'LATAM',
  {apn_geo} = 'GCR' OR {apn_geo} = 'ALL', 'GCR',
  'NULL'
)

This structure ensures that each region (APJ, EMEA, NAMER, LATAM, GCR) will include rows where {apn_geo} = ALL as well.

3

Hi @grazhang

Welcome to the QuickSight community!

I presume you want to have a Parameter to select the apn_geo and no matter what apn_geo is selected you want apn_geo = ‘ALL’ to also be selected.

In the visual’s filter for apn_geo you could filter based on a parameter and then add an OR clause where you provide ALL as the value.

image
image341×589 17 KB

Hope this helps.

4

This works perfectly- thank you so much!

1 Like