How can I create a calculated field in a pivot table that will count distinct values of Field1, given that Field2 meets a particular condition?
My limitations:
- Needs to be in a pivot table
- Needs to be a calculated field
- The calculated field needs to be a unique count "formula", not simply a field setting change
Here is an example of what I am trying to achieve.
Raw data:
╔═════════════╦═══════════╦═════════╗
║ Date Period ║ Client ID ║ Field 2 ║
╠═════════════╬═══════════╬═════════╣
║ 1 ║ A ║ 1 ║
║ 1 ║ A ║ 1 ║
║ 1 ║ A ║ 1 ║
║ 1 ║ B ║ 1 ║
║ 1 ║ B ║ 1 ║
║ 1 ║ C ║ 1 ║
║ 2 ║ A ║ 1 ║
║ 2 ║ A ║ 1 ║
║ 2 ║ B ║ 0 ║
║ 2 ║ C ║ 0 ║
║ 2 ║ C ║ 0 ║
╚═════════════╩═══════════╩═════════╝
Here is what the Pivot Table output would look like:
╔═════════════╦═══════════════════════════════════╦═══════════════════════════════════╗
║ Date Period ║ Distinct Clients where Field 2= 1 ║ Distinct Clients where Field 2= 0 ║
╠═════════════╬═══════════════════════════════════╬═══════════════════════════════════╣
║ 1 ║ 3 ║ 0 ║
║ 2 ║ 1 ║ 2 ║
╚═════════════╩═══════════════════════════════════╩═══════════════════════════════════╝
I have seen methods of using array functions (a combo of SUM, IF, FREQUENCY, and MATCH) for non-pivot table data. Can I do this with Pivot table fields?
I don't have any VBA background.
Aucun commentaire:
Enregistrer un commentaire