1 2 Previous Next 15 Replies Latest reply on Apr 11, 2018 10:58 AM by Mel Buckner

# pulling in calcs from report to report

I have two static spreadsheets that are left joined on date.

In them I am (first sheet) calculating how many patients were seen by each physician in the month of January 2018:

That part is working fine.
2nd page: calculating how many orders of each type of imaging the physician made in the month of January 2018:

Tableau is correctly counting the numbers of ct orders, us orders, vas orders and mri orders.

what it's NOT doing (and what I need it to do is correctly calculate the number of CT/US/Vas/MRI orders per 100 patients seen.
what seems to be happening is that when I pull in the distinct count of sheet 1, into the calculation to determine numbers of orders x 100 pts - it takes as the denominator for count of pts seen for example in the case of Andrea Banke - she saw 154 pts in the month of january and ordered 37 CT scans.  So, were I to take (154/37)*100 it should come out around 24.  not 0.4 -  because it's using the wrong denominator.  How do I get it to take the correct count of patients seen so the math works correctly?

can someone tell me what I am missing here?

• ###### 1. Re: pulling in calcs from report to report

Your [CT Per 100] Calc is this:

([CT Orders])/countd([CSN])*100

I added a calc that does COUNTD([CSN]) and that value is 8561 for Andrea Banke.

The math is correct.  Are you sure you want your CT per 100 calc to be dividing by that countD?

• ###### 2. Re: pulling in calcs from report to report

No – Andrea Banke saw 154 patients in January not 8561 – so the math is right but it’s pulling in too many patients (8561) – I want to pull in csn, and then have it know that only 154 of those to applied to banke, and so on down the list….how do I get it to assign the right patient count to each doc?

• ###### 3. Re: pulling in calcs from report to report

Hi Mel,

Use a level-of-detail calculation to determine the number of patients seen by physician, and this field can then be used in the numerator of the imaging-orders-per-100-patients calculation.

[Pats_Seen] = {FIXED [EDMD], DATETRUNC('month',[ADT_Arrival]): COUNTD(CSN) }

Be aware that this will calculate all patients seen by a physician, including those that are not "ED = ONB ED". To account for this, right-click on the "ED: Onb Ed" pill in the filters shelf and choose "add to context".  This context filter will also be necessary on Sheet 3. Alternatively, address this using the calculated field, such as:

[Pats_Seen] = {FIXED [EDMD], DATETRUNC('month',[ADT_Arrival]): COUNTD( IF [ED] = "ONB ED" THEN CSN END) }

The other filters - EDMD and ADT_Arrival - are levels at which the LOD calculation is being performed, so adding to context doesn't affect the result.  As more filters are added to the viz, keep in mind that they will be ignored by the level-of-detail calculation unless the filters are added to context.

• ###### 4. Re: pulling in calcs from report to report

You have different filters on the two sheets.

I added the same [ED] filter and EDMD filter to sheet 3 and still came up with 712 CSN count.  It's better, but still not correct.

But in a duplicate copy of SHeet 3 I swapped out OrdMD on ROWS and replaced it with EDMD.  You get the same number of doctors, but look at the [Number of Records] column I added.  Banke has 4816 records when the sheet uses OrdMD, but only 3730 records when using EDMD (on Sheet 3(2).)  And the copy sheet does come up with 154 for CSN.

So something is fishy with your data.

• ###### 5. Re: pulling in calcs from report to report

This is so close – but the ‘patient_seen’ counts are still lower than they should be..

• ###### 6. Re: pulling in calcs from report to report

well the only thing that could be fishy is maybe the join - it's two separate sheets, and they are joined on adt arrival date.

• ###### 7. Re: pulling in calcs from report to report

As I noted earlier, when you swap OrdMD and EDMD you get different counts.  They're coming from the different sides of the join, correct?  Something might be amiss in the way you are joining them.

and I just realized that I never uploaded the workbook I modified yesterday.  It is attached now.

EDIT:  Workbook removed for customer security reasons.

• ###### 8. Re: pulling in calcs from report to report

Ah yeah – I see what you are saying.  Yeah on one report is the emergency room physician (edmd) but other sheet is based on imaging orders which does to the MD the ordered the image but is not tied to the patients because of the way the imaging departments are set up.  All of that to say is that I want to pull the edmd totals and use it in the calculation to determine images divided by edmd totals all times 100

• ###### 9. Re: pulling in calcs from report to report

You'll have to use EDMC on Sheet 3, not OrdMD.

The measure is coming from that table.

If you create a dummy sheet and put EDMC on ROWS, and then OrdMD right after it on rows, you'll see a many-to-many relationship.  When you pull up COUNTD(CSN) under OrdMD, you get the crossing which results in a mega-number instead of 154.

• ###### 10. Re: pulling in calcs from report to report

in this you have the patients seen correct  but for some reason, something is making the numbers of images orders is like quadruple what it should be (and how it is in my workbook)...

• ###### 11. Re: pulling in calcs from report to report

On sheet 3(2)?  There I get 154 for the first doctor, because I'm using EDMC on ROWS.

• ###### 12. Re: pulling in calcs from report to report

Right – but next to it in CT orders- it should be 37 not 236

• ###### 13. Re: pulling in calcs from report to report

Oh, I think you are talking about a different field.  [US_Orders]?  Now you have the same problem in reverse.  You're not going to be able to get a proper count for a dimension from one table and a measure from another here because there isn't a correlation between the two.  You have a many-to-many relationship between the two.

Is there a way to hook up a doctor ID and associate it to the two key fields in the two tables, and join on that ID?

• ###### 14. Re: pulling in calcs from report to report

Same deal with CT Orders

1 2 Previous Next