1 Reply Latest reply on Nov 12, 2018 2:41 PM by Don Wise

    Calculating Average with LOD expression

    Kunal Patel

      Hello Tableau Gurus, I need help with the following data. I am working on financial data for a healthcare system that has 6 hospitals. I have rows of data with duplicate account numbers in multiple rows. I would like to calculate the average length of stay for each hospital. For instance, as seen in the table below I would like to calculate average LOS for Hospital X - which is (1+2+1)/3= 1.33 and for Hospital Y- (1+3)/2=2 . What LOD expression do I run on the account number and hospital to calculate average of unique/distinct cases only.

       

       

      HospitalAccount NumberALOS
      X
      1234561
      1234561
      1234561
      6789452
      6789452
      324561
      324561
      324561
      Y545641
      545641
      545641
      9639633
      9639633
      9639633
      9639633
      9639633
        • 1. Re: Calculating Average with LOD expression
          Don Wise

          Hello Kunal,

          I don't think you need an LOD for something that Tableau can do natively.  Please see attached 2018.3 workbook and below screenshots. I modified your data structure slightly so that it makes sense to me; hopefully, it's in line with how your data is structured.

           

          However, if you truly do need an LOD, so that you can remove ACCOUNT NUMBER, then use this:

           

          AVG({FIXED [Hospital],[Account Number]: SUM([LOS])/COUNT([LOS])})

           

          If this answers your question, please mark this response as correct. Thx! Don

           

          Screen Shot 2018-11-12 at 2.29.03 PM.png

          Screen Shot 2018-11-12 at 2.30.57 PM.png