2 Replies Latest reply on Apr 15, 2016 8:16 AM by Angie Tarbet

    Turning whole number into percent on table calculation

    Angie Tarbet

      Hi

       

      I've attached a sample workbook that I am hoping to find some help with.

       

      I'd like the key measurement to ALSO display as a percentage, along with the net total hours difference year to date in hours (as it displays now in the sample).

       

      I am having a hard time figuring out how to show the measurement, Net Available Hours Difference YTD from Prior Month as a percent? Once I figure that out, I'd likely drop that separate measurement in the view as a separate worksheet below or above the hours difference total.

       

      Any suggestions on how to make the whole number a percent is appreciated. Thanks as always for your expertise. Angie

        • 1. Re: Turning whole number into percent on table calculation
          Patrick A Van Der Hyde

          Hello Angie,

           

          Thank you very much for sharing the workbook with your sample data.  I reviewed the workbook and noticed that all of the Measures are not row level values but instead these are aggregate values for the Quarter Code, Quarter Dates, and office.  As a result, the calculations need to be either ATTR() or AVG() calculations to show correctly per row.  I also do not see a field named "Net Available Hours Difference YTD from Prior Month" in the workbook and I see a  few different fields that might represent the one desired.  Could you clarify the field desired?  In addition, if you know the values that would exist in the view - this would help to share since they would enable me to verify that the values derived are correct for what you are looking for?  

           

          Guessing here but window_sum(avg([Net Available Hrs]))  - I think this gets that value and also thinking you might be trying to get the difference between quarters rather than months which could be:

          (avg([Net Available Hrs])-lookup(avg([Net Available Hrs]),-1))/lookup(avg([Net Available Hrs]),-1)

           

          I changed all of the measures to utilize Measure Names/Values instead of displaying each as a separate column.  Hopefully this helps but knowing a bit more about the desired field and what value is expected would help me to help you more. 

           

          Patrick

           

           

           

          • 2. Re: Turning whole number into percent on table calculation
            Angie Tarbet

            Hi Patrick,

             

            What I am seeking is that the Net Available Hours Difference YTD from Prior Month be displayed as it currently is, as a numeric value, and then ALSO as a percent. I had it working last month before the quarter changed. When the quarter changed it threw off the table calculation, and I had to add to that cal in order to keep the numeric value displayed correctly, however that workaround I believe is what is causing the percent to not calculate properly now.

             

            The Net Available Hours Difference YTD from Prior Month (Net YTD field) calculation is now as follows:

            (Run along table across)

            WINDOW_SUM(SUM([Net Available Hrs]), -2, 0) - WINDOW_SUM(SUM([Net Avail Hrs (Arch)]))

             

            Getting to the percent should be simple, NET YTD / Net Avail Hrs (Arch) = percent

             

            But, it doesn't calculate correctly, the percent is not mathematically correct when you check what Tableau returns.

             

            I think that it has do with -2, 0 added to the table cal, pretty sure that is it because like I said, we had the percent working before the quarter changed earlier this month using the calculation above (Net YTD / Net Avail Hrs (Arch)).

             

            And, yes this dashboard is not "correct" in how we've used all kinds of fields, and workarounds to get to what we needed. And, the data is not the most friendly to begin with, agree!

             

            I need to revert the example you sent back me because we are still in 9.1.1. I will do this and add on to anything I may have missed in my reply to you as soon as able.

             

            In the meantime, I hope that the above has made what I am seeking more clear.

             

            Thank you! Angie