12 Replies Latest reply on Mar 15, 2018 9:19 AM by Amanda Lamb

    Median without duplicates

    Amanda Lamb

      There might be a very simple solution to this problem, but I can't figure it out. I want to calculate a simple median, but since some rows are duplicated it isn't coming out correctly.

       

      My dataset looks like the table below. The "Length" field is just CompleteDate-IntakeDate. I want to calculate the median based on the length field, but because there are duplicate ID nos (because of different "Types" in this faked sample), the calculation is wrong. For example, the median on the table below comes to 33.5, but if you remove the duplicate IDnos, you get 30.5. I want the 30.5 in Tableau. The purpose is to calculate the median length by intake year.

       

       

      IdnoTypeIntakeDateCompleteDateLength
      1CJanuary 3, 2017February 14, 201742
      2PJanuary 3, 2017February 7, 201735
      3CJanuary 5, 2017February 2, 201728
      4CJanuary 6, 2017February 3, 201728
      5PJanuary 6, 2017January 29, 201723
      6CJanuary 9, 2017February 14, 201736
      7CJanuary 10, 2017February 6, 201727
      9PJanuary 12, 2017February 14, 201733
      10PJanuary 13, 2017February 3, 201721
      11CJanuary 13, 2017February 16, 201734
      12CJanuary 18, 2017February 14, 201727
      12PJanuary 18, 2017February 14, 201727
      13CJanuary 18, 2017June 8, 2017141
      13PJanuary 18, 2017June 8, 2017141
      16PJanuary 19, 2017February 16, 201728
      17CJanuary 19, 2017May 2, 2017103
      17CJanuary 19, 2017May 2, 2017103
      17PJanuary 19, 2017May 2, 2017103