Calculated Field that counts unique values

[kbolliger]

Hello!


I've searched the forum and didn't find anything similar so I hope I'm not repeating a question!


I have a data set of about 30,000-40,000 rows. It is comprised of information for a Service Desk. It includes the date of the opened service ticket, the service name, product manager, department, incident number, Knowledge Base (KB) article number and KB usage.


I want to be able to count the number of unique KB articles used for each service and include it in the pivot table next to the number of tickets for the service.


A formula I would use if it was a regular table would be =SUM(IF(FREQUENCY(range,range)>0,1)). I tried inserting this into the Calculated Fields formula but when I put the Field into the Values it only returned a 1 for each service.


Is it possible to count unique occurrences?


I know I could just put the KB Number as a Row label under the Service but when there are hundreds of services and thousands of KB Articles it's not practical.


I have attached a worksheet that is organized the same as my workbook with generic data. It has the pivot table structure I was working with.


https://www.dropbox.com/s/i0ugvymvnsvcbre/CalculatedFieldGenericData.xlsx


Thanks!

 

[Trader273]

if you have your data and pivot table how you like it, then I'd look into the GetPivotData Function. Gives you all the nice things that come with pivot tables, while allowing you to use "regular" formulas.