Sum of total number of family per account and see all the families

[Touix]

Hello everyone,

Since 2 days I try to figure out my problem but i can not :(!

I have this data :

Date- Account -  family - amount

01/01/2021 - 001 - 1 - 100

01/01/2021 - 001 - 1 - 200

01/01/2021 - 001 - 3 - 200

01/02/2021 - 002 - 4 - 500

01/02/2021 - 002 - 5- 500

I want this result : 

 Account - Family - Number of family - Total amount of the family

 001 - 1-  2 -  500

 001 - 3-  2 -  500

 002 - 4-  2 -  1000

 002 - 5-  2 -  1000

I try with sum(aggr(   COUNT(  distinct family ),account)) but with no success.

If someone can help me , thank you so much.

I put account and family with dimension.

[stevejoyce]

you can do this with sum Total.

Total amount of the family:

Sum(Total <Account > amount)

and

Count(Total <Account> family)

[Touix]

Thank you for your reply Steve.

But What I want is the count of disctinct family actually. Sorry for my bad example : 

I have this data :

Date- Account -  family - amount

01/01/2021 - 001 - 1 - 100

01/01/2021 - 001 - 1 - 200

01/01/2021 - 001 - 3 - 200

01/02/2021 - 002 - 4 - 500

01/02/2021 - 002 - 5- 500

I want this result : 

 Account - Family - Number of family - Total amount of the family

 001 - 1-  2 -  500

 001 - 3-  2 -  500

 002 - 4-  2 -  1000

 002 - 5-  2 -  1000

[stevejoyce · Accepted solution]

With Account & Family as your table dimensions and expressions are:

Count(Distinct Total <Account> family)

and

Sum(Total <Account > amount)

[Touix]

Thank you very much Stevejoyce. it works well. It is something i have never seen. Thank you very much.

i have another question. Is it possible to put the family not in another line but with carriage return or a coma.

Exemple : 

 001 -    1,3    -  2 -  500

[stevejoyce]

You can use concat for this:

comma...

Concat(Distinct family, ',')

or carriage return...

Concat(Distinct family, chr(13))