topic Frequency in QlikView

Frequency

Thu, 16 Sep 2010 15:02:33 GMT

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

Thu, 16 Sep 2010 19:32:55 GMT

bump?

Frequency

johnw — Thu, 16 Sep 2010 19:40:16 GMT

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

Anonymous — Thu, 16 Sep 2010 19:42:04 GMT

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

Thu, 16 Sep 2010 20:08:45 GMT

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

Anonymous — Thu, 16 Sep 2010 20:16:15 GMT

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

Frequency

johnw — Thu, 16 Sep 2010 20:20:46 GMT

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

Thu, 16 Sep 2010 20:35:28 GMT

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

johnw — Thu, 16 Sep 2010 20:51:29 GMT

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

Thu, 16 Sep 2010 20:53:42 GMT

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.