2 Replies Latest reply on Dec 11, 2018 9:45 AM by Raheel Farooq

    Calculate median at month level

    Raheel Farooq

      Hi All,

      I am struggling calculating median value. The target or desired output is very simple, I want to throw MonthYear in the view and the median calculated field.

       

      Background:

           There are IDs and each id is associated with start and end dates.

           There is a derived field "days_on _service "based on start and end date. i.e. end_date - start date (days difference)

           There is RankbyId field that calculates ranking based on days_on_service field and in asc order

           There is final calculated field "Median" and that calculates Median for first 5ranks of each month.

      Problem:

           Median field is only working when I add ID field on the canvas either on color or detail or label or shape etc

      Desired Output:

           I need a line or tabular data with MonthYear and its calculated Median only so that there will be only one median value against each monthyear.

       

      Attaching is the tableau packaged sheet I am working on...

       

      Sheet1 is working perfectly fine, showing me monthyear and median side by side

      Sheet 2 is the trend line that is my ultimate goal..but currently the problem is if I remove the Id field from detail "median" calculated field gives an error.

      Sheet 2 and Desired output is same data and is also my desired output.

       

      Both twbx and xls source file is attached. Thanks

       

      Thanks

      Regards

      RF

        • 1. Re: Calculate median at month level
          Norbert Maijoor

          Hi Raheel,

           

          Not sure but find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.

           

           

          1. Define custom date: D1. Start Date (Month / Year)

           

          2. M1. index: index()

           

          3. M2. Median: int({fixed [D1. Start Date (Month / Year)]: countd([Id])}/2)+1........+1???

           

          4. M3. Display: [M1. index]=attr([M1. Median])

           

          5. Drag required objects to the indicated locations, hide header for Id & M1. Index & filter M3. Display on True

           

          index.png

           

          Hope it helps,

           

          Regards,

          Norbert

          • 2. Re: Calculate median at month level
            Raheel Farooq

            Thankyou Norbert for your response. Much appreciated!

             

            Yes this solution is working but still I have the same problem of ID field that we are bringing in the sheet. and this is creating performance issues where we have more than 100k distinct ids each month.

             

            I am trying to find a solution where I do not really need to bring the ID field on the canvas. I hope you are getting my point and what I am trying to achieve. Once again thanks for your time.

             

            Regards,

            Raheel