6 Replies Latest reply on Apr 14, 2016 9:00 AM by Brenda Fosmire

    LOD Fixed - is it only summing the first instance of a value?

    Brenda Fosmire

      I have data where one 'patient' would have multiple rows of data, and each rows would carries the patient's weight value. 

      (The weight does not change. It is just carried repeatedly in the data.)

       

      I want to get Average Weight for groups of patients. So I need to be sure and only sum the first instance of the patients weight.

       

      I had thought I would use a Min() or Max() to get the first single weight per patient to average.

      But then I saw this way to use LOD and it seems to work.

       

      Can someone assure me that I'm summing ONLY the first weight value?

       

      { FIXED [Patient Account]: AVG([Weight]) }

       

      I did a small hand calc to test my results and it seems correct. 

      BUT I really want assurance the this LOD is only summing the first (or only 1) of each patient's weight values?

       

      Or should I be using Min() or Max()?

       

      Thanks.

        • 1. Re: LOD Fixed - is it only summing the first instance of a value?
          Shawn Wallwork

          Well, you are getting the value you want, but not for the reason you think it is. If you average a set of rows that all have the same value, then you will get that value. It's not because you are only 'summing the first' of a patient's record. Now if you were really summing all these records then you would get an incorrect sum. And in your case it really doesn't matter whether you use AVG() or MIN() or MAX() you will always get the same value as long as the weight for all patient's record is always the same value.

           

          Make senses?

           

          --Shawn

          • 2. Re: LOD Fixed - is it only summing the first instance of a value?
            Brenda Fosmire

            Are saying that LOD FIXED is not taking the first, or min or max?

             

            Because I used the calc on a group of, say 100 patients (for which I had 10,000) rows of data, and it seems to give the correct Average Weight for the 100 Patients and not their 10,000 rows of weights.

             

            Maybe I should be using Min() and build an Average calcu with that?

            • 3. Re: LOD Fixed - is it only summing the first instance of a value?
              Shawn Wallwork

              You calculation is telling Tableau to average the [Weight] field for each Patient Account. You can use MIN() or MAX() or AVG, and you will get the same result. Think about it if you have 100 records for Patient 1 (or 1000 records), with a weight value of 10 then the average will be 10, the minimum will be 10 and the maximum will be 10. Right?

               

              Remember, you are assigning this single value to each Patient, you are not telling Tableau to assign this value to each patient record ID and then summing those 10,000 rows. Right? Ultimately it doesn't matter if these weights are assigned to every record and every patient as long as you don't sum them; as long as you use a non-additive aggregation.

               

              --Shawn

              • 4. Re: LOD Fixed - is it only summing the first instance of a value?
                Brenda Fosmire

                Thanks.  I want to fully understand what is happening in the LOD calculation.

                 

                I am trying to not make the stat error of taking an average of averages (Simpson's Paradox), where the first set of averages have different  denominators (or types).
                Why is an average of an average usually incorrect? - Mathematics Stack Exchange
                Simpson - Wikipedia, the free encyclopedia

                First my calculation is taking the avg of multi-rows of the same data.  BUT I think this may be safe because I know there is no variability in data being averaged. It is just a convenient way to get to a single value per patient.


                Next I tell Tableau to Avg(Patient Weight) - Caution! I"m taking an average of averages - Simpson's Paradox - may be in effect. 

                For some reason I think this is OK.  I wish I could soundly state why my use of this LOD Is not Simpson's Paradox.  My only answer is that the first average is all the same data.

                 

                Hum?

                 

                But you have answered my question of what the LOD is doing - you said it IS taking an average of all the rows per patient.

                 

                Thanks.

                • 5. Re: LOD Fixed - is it only summing the first instance of a value?
                  Shawn Wallwork

                  Next I tell Tableau to Avg(Patient Weight) - Caution! I"m taking an average of averages - Simpson's Paradox - may be in effect. 

                  For some reason I think this is OK.  I wish I could soundly state why my use of this LOD Is not Simpson's Paradox.  My only answer is that the first average is all the same data.

                  Yep, for the paradox to be in effect, the LOD average would have to return a different result than MIN() or MAX(). If it were different then you would actually be averaging something, and then averaging it again. To keep from confusing the issue I suggest you use MAX() instead of average.

                   

                  Cheers,

                   

                  --Shawn

                  • 6. Re: LOD Fixed - is it only summing the first instance of a value?
                    Brenda Fosmire

                    I agree - but I have not been successful at writing a Max() calc. 

                     

                    I tried this:

                    SUM(Max([Enc - Patient Account])) / COUNTD([Enc - MS DRG Weight])  - but the error is 'Argument to SUM (an aggregate function) is already an aggregation, and cannot be further aggregated.

                     

                    How would you approach writing a calc with Max()?