8 Replies Latest reply: Oct 18, 2010 5:18 PM by bryankoch RSS

    Sum by more than one distinct value


      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?

        • Sum by more than one distinct value
          Karl Pover


          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.


            • Sum by more than one distinct value

              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:

              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?

                • Sum by more than one distinct value
                  Karl Pover

                  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])


                    • Sum by more than one distinct value

                      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.

                        • Sum by more than one distinct value
                          Karl 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.


                            • Sum by more than one distinct value

                              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?