topic Re: Count of all compared with count based on selection in QlikView

Count of all compared with count based on selection

Anonymous — Tue, 08 Jul 2014 12:37:34 GMT

Hi all,

I am trying to measure product penetration among a client base by comparing total number of clients and number of clients holding 'X' where X is selected dynamically.

In tabular format, I need the output to look something like the following:

Banker	Total Clients	# Clients Holding X
A	25	15
B	12	6
C	31	15
D	11	1
E	10	0
F	70	7

I think the thing that is making this tricky is that I want X to be dynamic. I have a list box with stock names in it, e.g. Apple, Google, Microsoft. When I select one of these values, I want the #clients holding X to update but total clients will always be the same.

I have been experimenting using Count(Clients) for 'clients holding X' and Count(TOTAL Clients) for all clients but I am getting the same number for total clients for all Bankers.

I experimented with AGGR function but could not get this to produce the correct number either. I have a feeling this is a simple solution but I am drawing a blank...

Thanks,

Ben

Re: Count of all compared with count based on selection

Gysbert_Wassenaar — Tue, 08 Jul 2014 12:46:18 GMT

Something like this if you select only one stock:

count({<StockName={'$(=only(StockName))'}>} distinct Clients)

Re: Count of all compared with count based on selection

tresB — Tue, 08 Jul 2014 12:50:56 GMT

May be like:

=Count({<Clients>} Clients) // first expression for Total

=Count(Clients) // second expr for count based on selection.

Re: Count of all compared with count based on selection

Anonymous — Tue, 08 Jul 2014 12:52:54 GMT

Thanks Gysbert. With only 1 security selected I am still not getting the result I expect. Plus I would ultimately need to be able to select multiple securities as our naming in DB is not always great (e.g. might have Apple A, Apple B for example).

Using the code above is giving me a '0' value in each field even with only 1 selection for stock. Any other thoughts on how to do it?

Re: Count of all compared with count based on selection

Anonymous — Tue, 08 Jul 2014 13:12:50 GMT

Thanks but that is just giving me the same value for each of the expressions

Re: Count of all compared with count based on selection

tresB — Tue, 08 Jul 2014 13:16:35 GMT

That would give same values for both expressions only when there is no selection in the Clients field, otherwise, select a client value and see - it should give as you wanted.

Re: Count of all compared with count based on selection

Anonymous — Tue, 08 Jul 2014 13:33:33 GMT

Sorry - not what I am seeing here. Values are the same regardless of a single selection or not. The absolute result value is changing, but both expressions have same value. Looks like this is working for the selected value but the 'total clients' value is not fixing at total number of clients for each banker.

Re: Count of all compared with count based on selection

Anonymous — Tue, 08 Jul 2014 13:37:21 GMT

I think I have got it - Count({<Clients>} Clients) // first expression for Total : this should actually be

Count({<HOLDING>} Clients)

This seems to be returning me the correct results.

Many thanks!

Re: Count of all compared with count based on selection

matthewjbryant — Tue, 08 Jul 2014 13:40:47 GMT

Hi Ben Roberts,

Try using the following syntax on the Total Clients Column:

count({$<Clients=>} Clients)

Re: Count of all compared with count based on selection

Anonymous — Tue, 08 Jul 2014 13:45:05 GMT

Thanks - tried that too but did not work. Key must be use of the description field rather than client field in that expression. I have it working now. Many thanks all for help.