topic Re: count if sum if from a matrix in App Development

count if sum if from a matrix

Deekey — Fri, 23 Apr 2021 11:53:27 GMT

Hello, I got a question..

the table is as below
ProductType User Amount
TypeA Alice $10
TypeA Alice $10
TypeA Billy $10
TypeA Cathy $10
TypeA David $10
TypeB Alice $10
TypeB Billy $10
TypeC Billy $10
TypeC Cathy $20

I would like to calculate : no. of User who spent exact $10 in each ProductType

Expected result as below:
ProductType No.of User($10)
TypeA 3
TypeB 2
TypeC 1

I try to make a Table, the column was put the ProductType, and add the measure as :

Count(DISTINCT {<User={"=Sum(Amount)=10"} >} User)

But seem not work... anyone can advise.. thanks :@

Re: count if sum if from a matrix

GaryGiles — Sun, 25 Apr 2021 11:43:18 GMT

You can use the Aggr() function to test the sum of Amount for each ProductType & User like this:

=Count(Distinct if(aggr(sum(Amount), ProductType,User)=10,User))

Re: count if sum if from a matrix

JuanGerardo — Sun, 25 Apr 2021 16:14:03 GMT

Hi @Deekey, I think you are looking for:

Count({<Amount={10}>} Distinct User)

Re: count if sum if from a matrix

Deekey — Mon, 26 Apr 2021 01:31:25 GMT

Done. Thanks a lot 🙂