Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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!

View solution in original post

9 Replies
Gysbert_Wassenaar

Something like this if you select only one stock:

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


talk is cheap, supply exceeds demand
tresesco
MVP
MVP

May be like:

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

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

Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

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

tresesco
MVP
MVP

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.

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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
Creator II
Creator II

Hi Ben Roberts,

Try using the following syntax on the Total Clients Column:

count({$<Clients=>} Clients)

Anonymous
Not applicable
Author

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.