topic Multiple If in Expression in QlikView

Multiple If in Expression

Thu, 03 Nov 2011 22:03:53 GMT

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

Multiple If in Expression

swuehl — Thu, 03 Nov 2011 23:25:56 GMT

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

Multiple If in Expression

Fri, 04 Nov 2011 02:22:59 GMT

Thanks for the help.

Multiple If in Expression

IAMDV — Fri, 04 Nov 2011 15:01:40 GMT

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

Thanks - DV

Multiple If in Expression

Thu, 02 Feb 2012 06:39:29 GMT

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