Counting the number of occurrences of a particular value

[#1]

My problem is thus;

I have a table chart with following fields 'Account Holder ID' and 'Number of Accounts' (count(AccountNumber)). I would like if possible to build a table where it shows 'Number of Accounts' and 'Count of Account Holder ID' so that I can say, for example, there are 5 people wih 4 accounts, 10 people with 3 accounts and 1000 people who have only one account.

It makes no difference to me whether calculations occur within load script or within the table expressions.

Thank you in advance. Saul.

[johnw]

In a table, I think you could use this as a calculated dimension (or as an expression in a list box, or whatever):

aggr(count(AccountNumber),AccountHolderID)

For script, you could do this:

[NumberOfAccounts]:
LOAD
AccountHolderID
,count(AccountNumber) as NumberOfAccounts
RESIDENT AccountTable
GROUP BY AccountHolderID
;

Generally speaking, I try to avoid doing accumulations in script. They aren't responsive to selections, and they complicate the data model. But in this case, I'm not sure you even WANT it to be responsive to selections, and it might be worth it for the extra performance gained by avoiding a calculated dimension.

[#3]

I was so happy to get this working I forgot to post a message of thanks.

Many thanks John, that worked a treat.

Saul.

[Bernardo_Cambruzzi]

This really helped me! Thanks a lot!