topic Re: Count Multiple Conditions in QlikView

Count Multiple Conditions

Wed, 25 Jul 2012 17:50:30 GMT

I’m trying to count customers who are associated with a project and have interacted with an event (an interaction is denoted where the INT_ID is not null). My expression was successful with the following formula:

Count({<INT_ID={'*'}>} Distinct CUSTOMER)

I would like to add two additional conditions to the count:

Count only customers whose interaction category equals the project category. Something along these lines:

Count({<INTX_CATEGORY={PROJECT_CATEGORY}>} Distinct CUSTOMER)

Count only customers whose interaction date >=project date. I tried the following, but no luck.

Count({<INTX_DATE={">=PROJECT_DT"}>} Distinct CUSTOMER)

If possible, I'd like to wrap all three conditions into a single expression.

Re: Count Multiple Conditions

Wed, 25 Jul 2012 18:18:45 GMT

why don't you just add those three expressions together?

Count({<INT_ID={'*'},INTX_CATEGORY={PROJECT_CATEGORY},INTX_DATE={">=PROJECT_DT"}>} Distinct CUSTOMER)

Re: Count Multiple Conditions

Wed, 25 Jul 2012 18:19:31 GMT

You can use a comma to add multiple conditions. For example:

count({$<Int_ID={'*'},INTX_CATEGORY={PROJECT_CATEGORY},INTX_DATE={">=PROJECT_DT"}>}Distinct Customer)

Re: Count Multiple Conditions

Wed, 25 Jul 2012 18:23:42 GMT

Thanks for the replies. Per my original message, yes, I’m trying to merge them into a single expression. My problem isn’t with the structure of the expression. My problem is with the syntax for each of the two conditions shown below. I’m trying to count customers where the interaction category matches the value in the project category, and I want it limited to customers where the interaction date is on or after the project date. If you have any insight, that would be great.

Count({<INTX_CATEGORY={PROJECT_CATEGORY}>} Distinct CUSTOMER)

Count({<INTX_DATE={">=PROJECT_DT"}>} Distinct CUSTOMER)

Re: Count Multiple Conditions

Wed, 25 Jul 2012 18:36:59 GMT

Try the following syntax:

count({$<Int_ID={'*'},INTX_CATEGORY=PROJECT_CATEGORY,INTX_DATE={'>=PROJECT_DT'}>}Distinct Customer)

Re: Count Multiple Conditions

Wed, 25 Jul 2012 19:09:22 GMT

Thanks for offering help. I ran against a few more issues with the expression you suggested.

1. I'm not sure if this will make sense -- The PROJECT_DT field doesn't appear to be recognized by the Expression Editor as its own field. For instance, the other field names appear in brown formatting where PROJECT_DT is in black, even when I manually add back PROJECT_DT to the expression. Maybe the data format is not being recognized as a date to compare against?

2. When I apply the INTX_CATEGORY=PROJECT_CATEGORY portion of the expression separately, it evaluates to a count of zero.

count({$<Int_ID={'*'},INTX_CATEGORY=PROJECT_CATEGORY,INTX_DATE={'>=PROJECT_DT'}>}Distinct Customer)