Sum/Count If = Distinct

[#1]

Hello,

Could somebody please help with the below dilemma?

1. I would like to count all  Transactions that are NB? I think I got this one.

=Count({$<Transaction={'NB'}>}DISTINCT([Policy Number]))

2. I also need to count NB but down to Scheme level. I think I got this one too!

Count({$<[Scheme]={'1'}>}{$<Transaction={'NB'}>}DISTINCT([Policy Number]))

Count({$<[Scheme]={'2'}>}{$<Transaction={'NB'}>}DISTINCT([Policy Number]))

3. This is when my issues start. I would like to Sum(GrossPremium) if(Transaction='NB') and if the policy number is Distinct?

4. Finally I would like to Sum(GrossPremium) IF( Transaction ='NB' or 'CXL'  and if the policy number is distinct ( but the policy number will always be duplicated when there is a different transaction, what I need to do is remove all the duplicated rows I.E Row number 1,2 and 3 is duplicated so should only be counted/summed once).

Any help will be appreciated.

Thanks

Gary

Current Data

PolicyNumber	Transaction	Scheme	GrossPremium
1	NB	1	500.00
1	NB	1	500.00
1	MTA	1	175.00
2	NB	2	800.00
2	CXL	2	-800.00
3	NB	2	1100.00
3	NB	2	1100.00
1	NB	1	500.00
javascript:;1	MTA	2	100.00
3	CXL	2	-1050.00
2	CXL	2	-800

Required Straight Table

Total Sales	Scheme 1 Sales	Scheme 2 Sales	Total GWP	Scheme 1 GWP	Scheme 2  GWP
3	1	2	2400	500	1900

[Nicole-Smith · Accepted solution]

For #3:

=sum(aggr(distinct only({<Transaction={'NB'}>}GrossPremium), PolicyNumber))

For #4:

=sum(aggr(distinct only({<Transaction={'NB', 'CXL'}>}GrossPremium), PolicyNumber))

**EDIT:

For #2, you should write it like this:

Count({$<[Scheme]={'1'}, Transaction={'NB'}>}DISTINCT([Policy Number]))

Count({$<[Scheme]={'2'}, Transaction={'NB'}>}DISTINCT([Policy Number]))

[#3]

Please try like below:

1. Total Sales = Count({$<Transaction={'NB'}>}DISTINCT[Policy Number]) 

2. Scheme 1 Sales = Count({$< [Scheme]={'1'} , Transaction={'NB'}>} DISTINCT [Policy Number])

3. Scheme 2 Sales = Count({$<[Scheme]={'2'} , Transaction={'NB'}>} DISTINCT [Policy Number])

4. Total GWP = Sum({$<Transaction={'NB'}>} DISTINCT GrossPremium)

5. Total Scheme1 Sales = Sum({$<[Scheme]={'1'} ,Transaction={'NB'}>} DISTINCT GrossPremium)

6. Total Scheme2 Sales = Sum({$<[Scheme]={'2'} ,Transaction={'NB'}>} DISTINCT GrossPremium)

[#4]

HI Nicole,

Thank you for taking the time to respond to my post!!

Thanks

Gary