Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

ben2roberts
Contributor

Count of all compared with count based on selection

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:

BankerTotal Clients# Clients Holding X
A2515
B126
C3115
D111
E100
F707

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

Tags (3)
1 Solution

Accepted Solutions
ben2roberts
Contributor

Re: Count of all compared with count based on selection

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!

9 Replies
MVP & Luminary
MVP & Luminary

Re: Count of all compared with count based on selection

Something like this if you select only one stock:

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


talk is cheap, supply exceeds demand
MVP
MVP

Re: Count of all compared with count based on selection

May be like:

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

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

ben2roberts
Contributor

Re: Count of all compared with count based on selection

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?

ben2roberts
Contributor

Re: Count of all compared with count based on selection

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

MVP
MVP

Re: Count of all compared with count based on selection

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.

ben2roberts
Contributor

Re: Count of all compared with count based on selection

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.

ben2roberts
Contributor

Re: Count of all compared with count based on selection

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!

matthewjbryant
Contributor II

Re: Count of all compared with count based on selection

Hi Ben Roberts,

Try using the following syntax on the Total Clients Column:

count({$<Clients=>} Clients)

ben2roberts
Contributor

Re: Count of all compared with count based on selection

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.