topic Conditional count in charts in QlikView

Conditional count in charts

Wed, 21 Mar 2012 10:41:44 GMT

I have a problem implementing the following in QlikView (10)

I'm trying to count persons with a specific number of actions and visualize those values in a chart.

count(distinct person_id) where no_of_actions = 1

count(distinct person_id) where no_of_actions = 2

count(distinct person_id) where no_of_actions = 3

...

I calculate the number of actions using an if-statement:

if(item_typ=action, sum(action_quantity))

The "normal" way I see conditional count is

count(distinct if(no_of_actions=1), person_id)

I would however need to integrate the if-statement, because no_of_actions doesn't exist as a field, but is only calculated (also the number of actions should be seen per person). How can I achieve this?

Conditional count in charts

jvitantonio — Wed, 21 Mar 2012 11:05:25 GMT

Can you specify what no_of_actions is? A Variable? Try using set analysis. IF statement has an impact on performance.

count( {$<no_of_actions = {1}>} DISTINCT person_id )

Conditional count in charts

Wed, 21 Mar 2012 11:09:03 GMT

Hi,

Try this:

count( {$<person_id= {“=sum({<item_typ={"action"}>}action_quantity) = 1”}>} distinct person_id)

count( {$<person_id= {“=sum({<item_typ={"action"}>}action_quantity) = 2”}>} distinct person_id)

count( {$<person_id= {“=sum({<item_typ={"action"}>}action_quantity) = 3”}>} distinct person_id)

succes,

Halmar

Re: Conditional count in charts

Wed, 21 Mar 2012 11:20:04 GMT

no_of_actions is something I calculate, it's actually kind of non-existent
as mentioned above it's calculated: if(item_typ=action, sum(action_quantity))

Re: Conditional count in charts

Wed, 21 Mar 2012 11:27:36 GMT

Hello Halmar,

This looks like what I am looking for. I get a syntax error however, when I try to use it.

count( {$<person_id = {“=sum({<item_typ={"action"}>}action_quantity) = 1”}>} distinct person_id)

Unfortunately I am not familar enough with set analysis to solve it myself.

Susann

Re: Conditional count in charts

Wed, 21 Mar 2012 13:52:29 GMT

I solved it. It should be:
count(distinct {$<person_id= {“=sum({<item_typ='action'>}action_quantity) = 1”}>} person_id)