4 Replies Latest reply: Jan 3, 2018 3:16 PM by Jessica Malenfant

Display top N most frequent records in chart

Hi all - I'm trying to do something that I assume is a simple calculation that I'm struggling with for some time now.

Goal: display only the records that appear more than 2 times in a dataset.

Example of data:

TableIDICD-9 CodePatient IDVisit ID
12501A
22502B
32503C
4194D
54935E
64936F
71505G
94938H
104938I

I'd like to produce a list of all ICD-9 codes that appear more than 2 (or any #) of times in the data, including both the ICD-9 Code value and the count of how many times this unique code appeared

Example output

ICD-9 Code# of times the code was found in data (must be at least 2 times)
2503
4934

I've tried a few versions of this but can't seem to get it right

count( DISTINCT {<DX ={"=count(DX)>100"}>}DX), DX

If(Rank(Count(DISTINCT DX)) >100, Count(DISTINCT DX))

• Re: Display top N most frequent records in chart

Try this

Dimension

ICD-9 Code

Expression

Count({<[ICD-9 Code] = {"=Count(DX) >= 2"}>}DX)

• Re: Display top N most frequent records in chart

Here is a sample attached

• Re: Display top N most frequent records in chart

Thanks so much - I knew it had to be something simple!

• Re: Display top N most frequent records in chart

Create a Straight/Pivot Table

Dimension

[ICD-9 Code]

Expression

Count(Distinct{<[ICD-9 Code] = {"=COUNT(Distinct TableID) > 2"}>}TableID)

Or

Count({<[ICD-9 Code] = {"=COUNT([ICD-9 Code]) > 2"}>}[ICD-9 Code])