Help with expression denominator using constant

[#1]

Trying to figure out how to do the following.  Am calculating the percentage of readmissions to the hospital within 30 days by primary diagnosis and showing them as a percentage of all readmissions.

In this pop of 150,000 there were 3,989 readmissions out of a total of 66,496 admits.  If I create the following expression

=num(Count ( {$<Pat_Had_Readmit={'Yes'}>}Distinct conf_num) / 3989,'###.#%')

I get the correct percentage and if I click on one of the diagnoses it correctly calculates the percentage of the total.

BUT .... this is across the entire USA. I also have a multibox where you can filter on a number of different parameters, one of which is by State.  I want the same logic to occur and correctly calculate the totals and the percentage for any State that is chosen.  Obviously plugging the 3989 into this does not work as it calculates the percentage for the entire country.

I have tried a number of things including:

=num(Count ( {$<Pat_Had_Readmit={'Yes'}>}Distinct conf_num) / Count ( {$<Pat_Had_Readmit={'Yes'}>}Distinct conf_num),'###.#%')

Here, since the numerator and denominator are identical this always yields 100% even though it is accurately counting the readmits by State when State is chosen.  I have not been able to figure out how to get the State in there.

I think I either need to create the denominator as a constant or somehow include the logic that the State is part of the equation.  But I do NOT want to have to specify a State.  I want the default value and view to always be the whole country.  Since there are 50 states, DC, and a couple of territories I need to be able to create something that creates the default view of everything, but still allows the drill down when a State/Territory is chosen.  The actual value being used is StateAbbre.

Ideas?

[sunny_talwar]

Try this may be:

=Num(Count ({$<Pat_Had_Readmit={'Yes'}>}Distinct conf_num) / Count (TOTAL <State> {$<Pat_Had_Readmit={'Yes'}>}Distinct conf_num),'###.#%')

[#3]

Thanks Sunny. Appreciate the idea but I just tried it and it does not work.

[sunny_talwar]

May be I can provide a better help if you can share a sample?

[swuehl]

What is the context you are using this expression?

A chart with ... as dimension? I agree with Sunny that a sample QVW would definitely help to understand your setting.

[#6]

Is it possible just to keep and upload the chart only? Will that suffice?

Thx

[swuehl]

It would be best if you can upload a small sample QVW that demonstrates your data model as well.

You don't need to upload the business application, a sample with mock up data will do. Also try to limit the data model to the relevant fields and tables

(I agree, it's not easy to determine the relevant items, that job is most of the time also a big help for you to identify the underlying issue and possible solutions).

There is also the possibility to scramble field values (in settings - document properties), though a small sample should be created quite fast.

But it's definitely also giving a hint if you post all your chart dimensions / expressions you are using and maybe a screenshot of your data model.

[#8]

Hi Stefan,

Thanks for reaching out. Aside from the fact that I do not know how to do that (will need to research that as well), the underlying data is complex and the main claims table is over 17 million rows.

If it helps at all, here is the data model.

After running the query in SQL Server, I created a flag for patients who had a readmission to the hospital for the same diagnosis within 30 days of the original discharge. All admissions have a unique identifier as conf_num (confinement number) so you can easily identify admissions overall and also those that are readmits. The primary diagnosis is the Diag1 on the claim.

I am trying to be able to calculate the percentage of all readmissions by primary diagnosis.

So I count the number of readmissions by a given diagnosis and divide that by the total number of readmissions. If I plug the actual value for total readmits into the denominator, it works fine but you cannot drill down by State.

What I would like it to do is the calculate the number of readmits and divide it by the total number of readmits for that geocoded area, with the whole dataset (all US) being the default.

I think I can do it by creating a lookup table that would list all of the states and territories and then have a column with the total number of readmissions for that State and write a formula referencing that but I thought that there must be a direct way of doing this.

I think I could also do it with AND statements for each State or Territory but that seems like an awfully inefficient and long winded way to do things.

Here is what part of this tab currently looks like. The filter for state is on the left, the graph of diagnoses on the right and box in the middle is calculating the percentage. Like I said, it works great if I put the 3989 in the denominator but then you cannot drill down by state because it correctly calculates the percent of that States readmits for that diagnosis at the USA level, not the state level.

That help at all?

Steve

[swuehl]

If I understand your setting correctly you want to calculcate the percentage in a chart with a single dimension Diagnosis.

Then I think you should be able to get what you want simply by using the TOTAL qualifier, without a field list

=Num(Count ({$<Pat_Had_Readmit={'Yes'}>}Distinct conf_num) / Count (TOTAL {$<Pat_Had_Readmit={'Yes'}>}Distinct conf_num),'###.#%')

This should show the percentage of readmissions split over Diagnosis. And this still should be sensitive to e.g. making selections in State or Gender etc.

If you want to show the percentage in a text box, I am unsure what you want to show then. If this is the case, could you elaborate a bit more with a simple example?

[#10]

Thanks for suggesting this. I tried it but it is still not working.

I needed to get this done yesterday so I changed the approach and embedded the percent breakdown of the readmits by diagnosis in the chart and changed the text box to the total cost. A little different approach but it accomplished what I needed it to show.

Thanks for the help.

Steve