3 Replies Latest reply on Dec 6, 2018 1:32 PM by Jim Dehner

    Percent actual vs. expected

    Jenna Gravley
    Jenna Gravley Dec 6, 2018 10:54 AM

      I am trying to calculate the %enrolled (%actual) but my data is not set up in way that I can figure out how to make it work. The main problem is I have a category (includes enrolled and expected), and the number (ptidcount) for each category. I have made the following two formulas to calculate the number of enrolled and the number of expected, but when I then create a calculation for the % enrolled, I get 0%. I want to be able to calculate the % enrolled for any given time period and for any given protocol or site. In the attached workbook I need the Enrollment graph to have the % enrolled in the tooltip. I have also made a % Enrollment worksheet trying to accomplish the % enrolled in a table format. I have spent hours trying to figure this out and I just can't find a valid solution! Any help or advice is greatly appreciated!!!!

       

      • 53 Views
      • Categories: Actions & Filters, Calculations, Formatting & Display
      • Tags:
        • 1. Re: Percent actual vs. expected
          Jim Dehner
          Jim Dehner Ambassador Dec 6, 2018 11:05 AM (in response to Jenna Gravley)

          Hi

           

          Are you really on Tableau 7.2?

           

          this type of problem can be solved with LOD's but they were not available until 9

           

           

           

           

          would do it (note you may need to add other dimensions to the fixed statement before the colon -

          your workbook has many d/b's and sheets - i did not go through then

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Percent actual vs. expected
            Jenna Gravley
            Jenna Gravley Dec 6, 2018 11:36 AM (in response to Jim Dehner)

            Thanks Jim, this was very helpful!

             

            No, I am in Tableau version 2018.3, but version 7.2 of my dashboard draft

             

            I was able to get your suggestion to work on the overall protocol level, but when I try to add site into the fixed calculation the percents are incorrect.

             

            =correct % for protocol, no individual site %

            =incorrect % for protocol and site

             

            I have limited experience with LOD calculations, am I adding too many dimensions or adding them in incorrectly?

             

            Thanks so much for your help!

            • 3. Re: Percent actual vs. expected
              Jim Dehner
              Jim Dehner Ambassador Dec 6, 2018 1:32 PM (in response to Jenna Gravley)

              You are not using too many - in words the way you would read the statements is - for each combination of week, protocol, and site - it the category is whatever then pticount

               

              so you have created another layer in the data (don't look for it - it is virtual) but the important part is that the layer in NOT aggregated - so when you bring it to the viz you aggregate it - if you just plop it in the viz the aggregation will be SUM - that may not be what you want -

               

              so the 2 expressions will give you a count of Enrolled or Expected - then you can use those values like any other measure (btw I see I pasted the same calculation twice - it should have referenced Expected

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.