6 Replies Latest reply on Sep 13, 2018 11:49 PM by Steve Martin

    percentile

    Sara Salmina

      Hi

       

      I am quite new in Tableau, so I am still not very familiar with all formulas, aggregation, options,... I "inherited" the report from a person who left the company, therefore I am updating it because a few formulas don't work quite right.

      I have a table which reports the productivity of each person in a team on a weekly basis.

      The productivity is calculated using raw data: Actual Hours Completed/Actual Hours available: SUM({actual H compl])/SUM({actual Available H])

      I have a Report date filter where I can select multiple week.

      I then have an additional column which calculate the percentile of the productivity of the team (for simplicity, we can use the 50th percentile): {FIXED [Team Name], {Report Date] : percentile([actual H compl]/[actual Available h], 0.5)}.

      I have to rewrite the formula otherwise I get an error that "argument to percentile is already an aggregation"

       

      If I select only one week, all columns works fine.

       

      If I select multiple weeks, the percentile column does not work properly.

       

      To simplify, I thought to create a new column with only the results of the productivity (so to not have issue in relation to the aggregation) and then obtain the percentile from this column (which should contain only number instead of calc). The original "productivity column" (actual h completed/ actual h available) will be the same as the "new productivity column", but without calculation.

      The percentile column will have the same value on each row.

       

      It seems something very easy to imagine, but apparently not to realise.

      Are you able to help, please.

       

      Sara

        • 1. Re: percentile
          Steve Martin

          Hi Sara,

           

          His sounds as though itshoukd be easy to remedy with window calcs but in the absence of a workbook, this is difficult to visualise.

           

          Are you able to upload an anonymised wrkbook preferably using Superstore Sales for us to work with

           

          Steve

          • 2. Re: percentile
            Sara Salmina

            Hi Steve

             

            Thank you for your prompt replay.

            Please find attached screenshots to see what I mean (they are not actually attached...how can I attach something...???).

            Sorry, I know that is not the best format to get them, but because the report contains sensible information I cannot upload it... (and I honestly don't know what Superstore Shop is... sorry I am very new in this sector).

             

            Hope you can help with that, otherwise I will try to add details and see if I can upload something better...

             

            Thank you for your assistance.

             

            Best Regards,

            Sara

            • 3. Re: percentile
              Steve Martin

              Hi Sara,

               

              Switch to rhe advanced editor to add attachments.

               

              Also, Superstore Sales is the data set that ships with Tableau, all forum users are aware of this data and such are able to quickly troubleshoot problems without exposing our own companies to other company's data

               

               

              Steve

              • 4. Re: percentile
                Sara Salmina

                Thank you very much Steve

                • 5. Re: percentile
                  Sara Salmina

                  Hi Steve,

                   

                  where you able to understand my issue with the screenshots provided?

                   

                  Thanks.

                  Sara

                  • 6. Re: percentile
                    Steve Martin

                    Hi Sara,

                     

                    I am not able to see, neither will I be able to replicate the problem without a workbook to be able to provide a solution.

                    I appreciate that you cannot send me your data, so please can you either anonymise your source or re-build this using superstore sales and then attach the resultant packaged workbook.

                     

                    Steve