4 Replies Latest reply on Feb 1, 2019 6:50 AM by Tim Dines

    Average Active Time

    Justin Smith

      Hi there Fam.


      Ive been trying to build this report and im struggling here. I have searched through the forums and found sum helpful topics but im still struggling to perfect this.


      I need to create a report of the average time a user is active on our system. Ie the average time between first transaction and last transaction.

      so I have a table with user_id's and created_at dates and so I can already see on a user by user basis what the difference is between a users first transaction and a users last transaction but I need to see this as an average over all of our users.


      Your help is greatly appreciated 

        • 1. Re: Average Active Time
          Zhouyi Zhang

          Hi, Justin


          Without seeing a sample workbook, it is not easy to tell how to get yoru expected result, can you provide one?



          • 2. Re: Average Active Time
            Tim Dines

            It sounds like all you need to do is take the sum of the lengths of time the user is online each time and divide by a count of the number of times they are online.  Really can't be of much help without a sample of your work.

            • 3. Re: Average Active Time
              Justin Smith

              I have attached a sample workbook. I had to create a seperate workbook with sample data as my actual workbook is connected to a secure servers database.


              as you will see in this example i have 3 custom calculated fields


              #1 first transaction getting the first transaction by the created at date. 

              #2 last transaction getting the last transaction by created at date.

              #3 difference between these 2 dates in days.


              I have added a user id filter just to show that i have the result for an individual user


              but what i need is the difference in dates  as an average on all the users

              • 4. Re: Average Active Time
                Tim Dines

                This will give you what you are looking for.  This LOD looks at the datediff values based on the User ID and then we take that and get the average.


                AVG({ FIXED [User Id]: [Difference between dates]})