Multiple If in Expression

[#1]

I have the following Example data

I need to find Number of Users who never used Card but used only Key.

I set Dimension as Country

For Expression I have used the following.

Count (DISTINCT(If  (CardUsageCount=0, (If (KeyUsageCount>0, UserID)) )))

I am supposed to get results as follows

Country                  No Of Key Users wo never used card

GERMANY                    1

North. AMERICA             0

BULGARIA                    0

Bulgeria should not be counted because  the user has used card in August.In my result it is counted.

Where am I making mistake?

Thanks

[swuehl]

I believe your if() statement is evaluated per record and you have two records for bulgaria, one of it results in a true condition and thus you get a count for bulgaria.

Try this instead:

=Count(distinct aggr(If  (sum(CardUsage)=0 and sum(KeyUsage)>0, UserID),UserID))

this aggregates CardUsage and KeyUsage per userID over all Month ("never used Cards, ever used Keys") and returns appropriate UserIDs.

Hope this helps,

Stefan

[#3 · Accepted solution]

Thanks for the help. 

[IAMDV]

Please can you mark the right answer as "Correct Answer"? It will be good for other users to see the correct answer.

Thanks - DV

[#5]

Please mark the answer as correct, not your own comment ;-). Enjoy the day!