9 Replies Latest reply: Sep 16, 2010 4:53 PM by bryankoch RSS

    Frequency

    bryankoch

      Hi,

      I'm having trouble figuring out how to do a frequency count of non-continuous data.

       

      For example, I'd like to make a chart that looks along the lines of this:

       

      Taxonomy Category Accepted Accepted % Denied Denied % Total

      Cardiology 50 33% 100 66% 150

      Ophthalmology 25 25% 75 75% 100

       

      Accepted and Denied are both values within the [Claim Status] variable. I thought perhaps a where clause would work, such as count(ClaimStatus) where ClaimStatus = "Accepted", but that doesn't seem to work. Then there's the issue of figuring out how to add the % columns.

       

      Can anyone point me in the right direction?

      Thanks

        • Frequency
          bryankoch

          bump?

            • Frequency
              John Witherspoon

              Maybe this?

              dimension:
              Taxonomy Category

              expressions:
              Accepted = count({<ClaimStatus={'Accepted'}>} distinct ClaimID)
              Accepted % = Accepted / Total
              Denied = count({<ClaimStatus={'Denied'}>} distinct ClaimID)
              Denied % = Denied / Total
              Total = count(distinct ClaimID)

            • Frequency
              Michael Solomovich

              Assuming you have something like ClaimId, it could be
              Accepted = count({$<ClaimStatus={'Accepted'}>}, ClaimId)
              Accepted % = count({$<ClaimStatus={'Accepted'}>}, ClaimId) / count(ClaimId)
              Similar for Denied

                • Frequency
                  bryankoch

                  I'm getting a bad field name error when i try either of your responses. It doesn't like the Accepted at the far left that we're trying to declare as a variable.

                    • Frequency
                      Michael Solomovich

                      Can you show data model? We can't guess correct field names...

                        • Frequency
                          John Witherspoon

                          We're not trying to declare "Accepted" as a variable. We're writing shorthand for "You should create a column with the label Accepted that has the definition of count({<ClaimStatus={'Accepted'}>} distinct ClaimID)". But as Michael says, we don't know your field names, which could be another problem.

                          • Frequency
                            bryankoch

                            Basically for this chart, I have a data set that has these fields:

                            Taxonomy Category

                            CPT Category

                            CPT Code

                            Allowed

                            ClaimStatus

                             

                            The idea is to be able to drill down with the group (Taxonomy Category -> CPT Category)

                            ClaimStatus is based on the Allowed variable. Allowed is a continuous numeric variable. If Allowed is greater than zero, the claim is accepted. If Allowed is less than or equal to zero, then the ClaimStatus is denied. The claims can have more than one CPT code, and while if one CPT is denied - then the whole claim is denied, our focus is which CPT codes are being denied. For this reason, the counts are based on the CPT codes.

                            What I've done in excel before put the data into a pivot table and set the ClaimStatus as the Column Label, the "Drill" group as the row labels, and then put the sum of the count in the values field.

                            where the table literally has the headers:

                            Claim Status

                            Groups Accepted Accepted% Denied Denied% Total

                             

                            Hopefully that's the information you needed.

                              • Frequency
                                John Witherspoon

                                Well, other than using the right field names, and telling you to make a straight table instead of a pivot table, I don't have any different answer for you:

                                dimension:
                                Groups

                                expressions:
                                Accepted = count({<ClaimStatus={'Accepted'}>} distinct "CPT Code")
                                Accepted % = Accepted / Total
                                Denied = count({<ClaimStatus={'Denied'}>} distinct "CPT Code")
                                Denied % = Denied / Total
                                Total = count(distinct "CPT Code")

                                And it works just fine. See attached.

                                • Frequency
                                  bryankoch

                                  OH, I see, type it in the expression field as

                                  count({<ClaimStatus={'Accepted'}>), ClaimID)

                                  not as Accepted = count({<ClaimStatus={'Accepted'}>), ClaimID)

                                   

                                   

                                  Again, thanks for your help gentlemen.