4 Replies Latest reply on Oct 30, 2018 11:16 AM by Nicole Lohr

    Average number of records

    Christine Curtis

      This is a super simple problem that shouldn't be a problem. I am using the free trial version until my IT group corrects my license. I'm not sure if that is my problem but I can't use the quick button changing the "number of records" from SUM to AVG anymore. I'm wanting to look at average number of visits in a month by PCP. SUM works fine. I can see old worksheets (under my old license) where AVG worked just fine but using daily census. Now I just get a "1" whenever I choose AVG. Has anyone else seen this? I know I can create a calculation for this but I never had to before.

       

        • 1. Re: Average number of records
          Jason Scarlett

          The answer to your specific question is that the average of 1 is 1.

          Remember that the "Number of Records" is essentially a column in your data sources that is "1".

          .. the key is .. but using "daily" census .. that is a totally different measure/column in your data source.

          Jason

          • 2. Re: Average number of records
            Christine Curtis

            Sorry, I'm still confused. If I have 132 records in a month and I want to know what's my average per day I used to be able to select AVG versus SUM. This no longer works.

            • 3. Re: Average number of records
              Jason Scarlett

              I see another calculation called "Avg number of records" ... what is that? Maybe someone has already created the calculation you need.

               

              To get the average number of records PER DAY, you need to count the number of records in a month and then divide it by the number of days in that month. You can't expect Tableau to know when a user wants a per DAY average versus a per MONTH average versus a per RECORD average.

               

              Something like this (i'm doing this from memory with no Tableau Desktop to test on):

              // total number of records each month

              SUM([Number of Records])

              /

              // divide by the number of days in the month

              // get the days in the month by counting the difference in days between two dates

              // The second date is the original date with one month added to it.

              SUM(DATEDIFF('day', DATETRUNC('day',[Date]), DATETRUNC('day',DATEADD('month',[Date],1)))

              • 4. Re: Average number of records
                Nicole Lohr

                Christine, is your data one row per patient or multiple rows per patient?  If its multiple rows per patient, taking the average of the sum of records wouldn't get you the desired result.  When you use Tableau default number of records, its just counting the number of rows in your data set.  It doesn't explain why the calculation worked before and not in the new version.