Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
Something like this if you select only one stock:
count({<StockName={'$(=only(StockName))'}>} distinct Clients)
May be like:
=Count({<Clients>} Clients) // first expression for Total
=Count(Clients) // second expr for 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?
Thanks but that is just giving me the same value for each of the expressions
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.
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.
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!
Hi Ben Roberts,
Try using the following syntax on the Total Clients Column:
count({$<Clients=>} Clients)
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.