These are my current data
ACCOUNT NUMBER PRODUCT STATUS RANK DESIGN
1 530033 Wealth Services Closed 1 Manual
2 530033 Wealth Services Closed 2 Manual
3 530033 Wealth Services Closed 3 Manual
4 530033 Wealth Services Closed 4 Manual
5 534656 Initial Escrow Open 1 Manual
6 534656 Initial Escrow Open 2 Manual
7 535161 Markets Integrity Closed 1 Manual
8 538379 Prepaid Cards Closed 1 Manual
9 538379 Prepaid Cards Closed 2 Manual
10 538379 Prepaid Cards Closed 3 Manual
11 538379 Prepaid Cards Closed 4 Manual
12 538915 Uploaded Cards Open 1 Manual
13 538915 Uploaded Cards Open 2 Manual
14 538915 Uploaded Cards Open 3 Manual
I would like to create a column that would uniquely count every duplicate ACCOUNT NUMBER. For example Account number 530033 appeared four times, I would like to count every 530033 as one, same with the other account number 534656 appeared twice is till be counted as one.
I've already tried the following but none of these work.
count(distinct(Account Number))
count(distinct(Account Number) for Account Number)
with Aggregate columns equals to TOTAL, COUNT or AUTOMATIC.
I will use the data to create a cross-tab. My cross-tab gives me a a TOTAL count of 14 because it counts all duplicate account numbers. Because all account number have fourteen rows. But in reality there's only 5 account numbers (530033, 534656, 535161, 538379 and 53891)
TOTAL Open Closed
Wealth Services 4 4
Initial Escrow 2 2
Markets Integrity 1 1
Prepaid Cards 4 4
Uploaded Cards 3 3
TOTAL 14
Ideally it should be like this.
TOTAL Open Closed
Wealth Services 1 1
Initial Escrow 1 1
Markets Integrity 1 1
Prepaid Cards 1 1
Uploaded Cards 1 1
TOTAL 5
Should I create a new column named Unique count of account numbers? but how do I count the account number individually?
Do I have to create another table with unique account numbers then inner join both tables?
I believe the expressions you need are the following:
TOTAL
COUNT(DISTINCT [ACCOUNT NUMBER] for [PRODUCT])
Open
CASE [STATUS]
WHEN 'Open' THEN COUNT(DISTINCT [ACCOUNT NUMBER] for [PRODUCT],[STATUS])
ELSE NULL
END
Closed
CASE [STATUS]
WHEN 'Closed' THEN COUNT(DISTINCT [ACCOUNT NUMBER] for [PRODUCT],[STATUS])
ELSE NULL
END
Make sure you set the Aggregate Function for each data item to 'Calculated' since we are manually specifying the aggregate and rollup.