5 Replies Latest reply on Oct 17, 2018 9:07 AM by swaroop.gantela

# Annualizing by contract year

Hello,

I am looking to auto-annualize physician wRVUs for their contract year and month. For example, I have 2 physicians with different contract start dates and I update their wRVUs monthly but currently I do the annualization manually. Physician A contract term runs from 5/1/18 to 4/30/2020 and Physician B runs from 7/1/2016 to 6/30/2021. I currently have their wRVUs updated for each month up until September. So for Physician A's wRVUs to be annulaized I take the sum of May-Sept divide  by 5 and multiply by 12, for Physician B its July - September divided by 3 multiplied by 12. What I am hoping to do is to calculate how many contract years are within a contract term, and somehow have Tableau recognize that I have data for however many months of each contract term so that I can create a calculation that will automatically annualize for each physician.

I have attached a workbook with each physician wRVUs.

Thank you!!

• ###### 1. Re: Annualizing by contract year

Nicole,

I'm not quite sure that I caught the gist, but I made an attempt.

I first set the default properties of Date to have a fiscal year start of October

Fiscal Dates

Then I made some Year and Month custom dates using

Custom Dates

I joined the two sheets on Physician and made a Boolean check

for what dates to keep:

[Date]>=[Contract Start Date] AND [Date]<=[Contract End Date]

So, as I understand it, the goal is account for the

partial year when a Physician starts the contract

or the partial year when a contract ends.

// if partial year because monthcount < 12
IF
{ FIXED [Physican],[Date (Years)]:
COUNTD(IF [ContractMonths] THEN [Date (Months)] END)}<12

// for this physician, fy add up wRVUs only for contract months

THEN
12 * { FIXED [Physican],[Date (Years)]:
SUM(IF [ContractMonths] THEN [wRVUs] END)}
/
{ FIXED [Physican],[Date (Years)]:
COUNTD(IF [ContractMonths] THEN [Date (Months)] END)} // for this physician, fy count contract months

// otherwise add up all wRVUs for the complete year

ELSE
{ FIXED [Physican],[Date (Years)]:
SUM (IF [ContractMonths] THEN [wRVUs] END)}
END

Please see workbook v10.3 attached in the Forum Thread.

If this is not matching the goal, please post what the

desired numbers are in the attached spreadsheet.

• ###### 2. Re: Annualizing by contract year

Thank you for responding! I have attached the excel worksheet and on the desired number tab, I tried to explain better what I am looking for.

• ###### 3. Re: Annualizing by contract year

Nicole,

Thank you for the expected result.

Hmm. I made a second attempt, but it took me quite a lot more steps than I'd expected.

There is very much likely an easier way to go about it.

But at the least, maybe what's presented here could give ideas.

One issue is the need to pad out months that do not exist in the dataset,

namely the months that need to be annualized.

There are probably some good in-Tableau methods to do this.

I opted for adding a new sheet of a Month Scaffold with a large range of future months.

(This likely was the source of my complications.)

So I took to Tableau Prep to join the <contract term>  to the <scaffold months>.

Then I joined <wRVUs> to the <contract term>.

Then I joined those two together. Join diagram below.

In Tableau Desktop, I needed to weed out some extraneous joins so

I needed this to get the true wRVUs:

IF [DateMonth]=[ScaffoldMonths]

THEN [wRVUs]

ELSE 0  //this will be used to signal when to used annualized amount

END

The next step was to segregate the Contract years using:

INT(DATEDIFF('month',[Contract Start Date],[ScaffoldMonths])/12)

Then the annualized amount became the total amount for the Contract Year / non-zero months:

{ FIXED [Physican],[ContractYearDiff]:SUM([RVU Filtered])}/

{ FIXED [Physican],[ContractYearDiff]:COUNTD(

IF [RVU Filtered]>0 THEN [DateMonth] END )}

Lastly, made a choice to return either the wRVU or the Annualized amount:

IF SUM([RVU Filtered])>0 THEN SUM([RVU Filtered])

ELSE SUM([Annualized])

END

Of note, I didn't work hard on enough on the Table Calculation settings

to get it show multiple Physicians stacked, so it currently can only

show one physician at a time.

There certainly must be a more straightforward way to do this.

Open to other suggestions from the community.

Attached in the Forum Thread are the twbx, the tflx, and the xlsx.  • ###### 4. Re: Annualizing by contract year

that worked, thank you!! but is there any way to filter so it only shoes the contract year with data in them? For example in the picture above, I only want it to show july 2016 to june 2019.

• ###### 5. Re: Annualizing by contract year

Nicole,

You can use this method to create a filter that

will not interrupt the running total:

https://kb.tableau.com/articles/howto/filtering-view-without-filtering-underlying-data

In the attached, the filter is:

LOOKUP(ATTR([ScaffoldMonths]),0)

and its table settings are the same as for the wRVU pill.

Please see the workbook v10.3 attached in the Forum Thread. 