Multiple filters in dataset with AND & OR conditions

AvinashK

Name	City	Troops	Support
A	XYZ	10	Y
B	XYZ	30	N
C	PQR	20	Y
D	PQR	40	N
E	MNO	30	Y
F	MNO	10	N
A	MNO	20	Y
B	PQR	40	N
C	XYZ	25	Y
D	MNO	35	N
E	PQR	45	Y
F	XYZ	5	N
A	PQR	40	Y
B	XYZ	25	N
C	MNO	35	Y
D	PQR	45	N
E	XYZ	5	Y
F	MNO	20	N

 

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 

MarkSnodgrass

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.

MichelleH

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

Mr_53

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