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

    Annualizing by contract year

    Nicole Celko

      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
          swaroop.gantela

          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
            Nicole Celko

            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
              swaroop.gantela

              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.

               

              284257join.png

               

              284257graph.png

              • 4. Re: Annualizing by contract year
                Nicole Celko

                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
                  swaroop.gantela

                  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.

                   

                  284257settings.png