Sum by more than one distinct value

[#1]

I'm sure there's a simple way to do this, I think I might not be looking in the right place.

I have a pivoted table and I need to sum up the [Number of Denials] by distinct [Claim ID] and distinct [CPT].

Is there a simple way of doing this?

[pover]

Bryan,

Could you please explain your problem with some sample data?

Maybe a sum(aggr(sum([Number of Denials]),[Claim ID],[CPT])) will work, but it might be overkill.

Regards.

[#3]

Here's a rough setup of the dataset.

Basically, in the original data set, the general lay out is this:

There's the claim. In each claim, there can be one to many CPT codes. These codes are the most specific detail in the data set.

Each claim can have one to eight different ICD codes and placed in the corresponding column they were put in (hence ICD1, ICD2, ICD3, ICD4..). Because of this, each CPT code within the claim level will have the same diagnosis codes. I designed this dataset as a prototype and all rows in ICD1 will be filled, but not necessarily in ICD2, ICD3, ICD4.. etc.

When I pivoted the table I did so in respect to the ICD codes:

ICDTable:
CROSSTABLE (ICDVariable, ICDNumber, 12)
LOAD recno() as ID, [Taxonomy Category], [CPT Category], [CPT], [CPT Descriptions], [Claim Status], [Number of Denials], [Facility Name], [Customer Name], [Provider Name], [Payer Name], [Claim ID],
ICD1, ICD2, ICD3, ICD4, ICD5, ICD6, ICD7, ICD8
Resident Prototype;

Since when I pivot this, I now have ~ 4 times the amount of ICD codes than I should have. What I'm trying to do is create a table chart with a cyclic dimension (Taxonomy Cat, CPT Cat, CPT, ICD Cat, ICD Number) by claim status (accepted or denied) so that way it will show the count of ICD codes appropriately. In order to take out the watered down codes I need to count by unique CPT and Claim ID combinations.

I did try your suggestion, but in the table chart, it just returns - across all of the elements of the dimension.

here's where I started on the expression:

count({$<[Claim Status ICD]={'Accepted'}>} distinct [Claim ID ICD])

I just need to somehow have the above function count this by the distinct Claim ID and CPT.

Does that make sense?

[pover · Accepted solution]

I'm getting there. I understand the raw data more, but I still don't understand what result you want. What should the simple table show given the data above?

And just to make another shot in the dark. What about using a

count({$<[Claim Status ICD]={'Accepted'}>} distinct [Claim ID ICD] &'_'& [Claim ID] &'_'&[CPT])

Regards.

[#5]

I had actually just tried the & in there, but for some reason the numbers still aren't correct. So then I thought I needed to add the date of service in because sometimes a claim can have a couple of the same cpt codes but with different dates of service.

count({$<[Claim Status ICD]={'Accepted'}>} distinct [Claim ID ICD] & [CPT ICD] &[Date of Service ICD])

The numbers still aren't matching up.

I'm working on figuring that out right now. I know of one issue, but that would cause there to be more than the 9,999 number I'm looking for.

I attached 2 charts, the 1st one in green is the one I'm working on. The 2nd in blue is what the 1st one should look like if the calculations are right. The difference is one table is using pivoted data and the other isn't. Yes I could cheat and just use the blue chart, but then my selection options would not work on that chart because the data is from another table.

[pover]

It may seem unnecessary, but the & '_' & between the fields in case you have the situation you get a value 111 where it could be Column 1 = 1 and Column 2 = 11 or Column 1 = 11 and Column 2 = 1.

There are 2 records that will be counted as 1.

Regards.

[#7]

I think I've finally settled on results I'm happy with. Most of the expressions looks similar to:

count({$<[Claim Status ICD]={'Accepted'}>} distinct [Claim ID ICD] &'_'& [CPT ICD]&'_' &ID)

What I did is I removed a join that creating more rows that it should (Which is another problem I want to ask about) and I realized that not all CPT&Claim IDs in my prototype are unique, so I used the row ID created in the cross table and this produced the results I'm looking for.

As far as the other question...

In the data set I have the ICD codes and in another set I have a master list that provides the description and category for each unique code. The problem is the master list is poorly designed in my opinion. For example, there is a difference between 785 and 785.0 in descriptions. The only solution I can come up with is to have anything associated with the ICD codes imported as text so there is no rounding or truncating.

Is there a function that can be used in the script to force a field to be explicitly read as text?

Thanks!

[pover]

Try the text(trim()) functions to clean up the data and turn it into text. I've never figured out why, but yes things like this sometime happen en QlikView when doing a applymap() o join.

Regards.

[#9]

That worked like a charm.

Thanks for your help Karl.