Multiple filters in dataset with AND & OR conditions

NameCityTroopsSupport
AXYZ10Y
BXYZ30N
CPQR20Y
DPQR40N
EMNO30Y
FMNO10N
AMNO20Y
BPQR40N
CXYZ25Y
DMNO35N
EPQR45Y
FXYZ5N
APQR40Y
BXYZ25N
CMNO35Y
DPQR45N
EXYZ5Y
FMNO20N

 

My dataset looks something like this with lot many rows. I want to find the sum of troops A has with Support (Support is Y) and for only XYZ and MNO Cities. 

 

 

I have tried something like this 

 

Case

         when support = y then

                 case when city = MNO or city = XYZ then

                     troops

                 end

end 

 

I use both or & and at the red colored portion above but I was not able to get the required number. 

 

Please help me 

Comments

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    I'm not sure what card you are wanting to use to display your result, but have you tried using the filters to only show the items you are looking for? You shouldn't even need to do an elaborate CASE statement.

    Based on what you said, I would drag Name into the filters section and select A. I would also drag Support into the filters and select Y. Finally, drag City into the filter and select XYZ and MNO. You could then use something like a single value card and drag Troops into the value and choose SUM for your aggregation. This should get you what you are looking for.

  • If you're having trouble with the OR, then you can break city MNO and city XYZ into separate lines in the CASE statement. You can do something like this:

     

    case
    when `City` = 'MNO' and `Support` = 'Y' then `Troops`
    when `City` = 'XYZ' and `Support` = 'Y' then `Troops`
    else 0 end
  • In my experience when using AND & OR conditions parenthasis are key, I would write it like this:

    case
    when (support=y and (city=MNO or city=XYZ)) then Troops
    end