4 Replies Latest reply on May 3, 2018 3:57 PM by Wesley Magee

    Using datediff to calculate total age based on multiple timestamps

    Sara Bradberry

      Going to do my best to explain and apologize in advance that I cannot publish a workbook as the one I am working on has information that cannot be public.  I have the following formula which gives me the number of seconds between each timestamp associated with a document ID.  Each document ID has multiple "timestamps" as it is being processed.  I need to be able to manipulate this so that I can essentially calculate a total "handle time" by document TYPE by aggregating up the total age of each document ID of that type.  The formula I am using currently is as follows:

       

      datediff('minute',lookup(ATTR([timestamp]),0),LOOKUP(ATTR([timestamp]),1))

       

      which gives me a result that is calculated using each document ID as a new starting point - see example:

       

      I am now trying to aggregate this function so that I can look at it at a higher level than every single individual document and trying to use something like this:

       

      IF [doc_status]="New" THEN datediff('minute',lookup(ATTR([timestamp]),0),LOOKUP(ATTR([timestamp]),1)) ELSE 0 END

       

      but receive the following error: cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions.

       

      I am stumped, any ideas? Ideally, this would just be a table with, for example, doc type and then the average handling time for that doc type (in this screen shot, the doc type is A1).

        • 1. Re: Using datediff to calculate total age based on multiple timestamps
          Wesley Magee

          Sara,

          Another possible approach would be to use a level of detail expression. We could first account for the total time for each document:

           

          Document Chronicle Time

          {FIXED [document_chronicle_id]: SUM(DATEDIFF('minute', MIN(timestamp),MAX(timestamp)))}

           

          You could use this in a view with document_type to get the total time by document_type or you could create another LOD expression:

           

          {FIXED [document_type]: SUM(Document Chronicle Time)}

           

          Let me know if this isn't what you were looking for.

          -Wesley

           

          If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Using datediff to calculate total age based on multiple timestamps
            Sara Bradberry

            I COULD ABSOLUTELY CRY, thank you thank you THANK YOU!  The only thing I will add is that because the datediff is already an aggregation, I was getting an error using SUM and DATEDIFF so I just had to remove "SUM" from your equation and it worked like a dream.

            • 3. Re: Using datediff to calculate total age based on multiple timestamps
              Sara Bradberry

              One question, since this is my first time using an LOD expression, I'm not totally familiar with how they work.  From this calculation I need to also solve for excluding weekends and holidays as well as being able to exclude when the documents are in certain statuses IE, exclude if doc_status = "Done". 

               

              Are LODs able to handle complex and multiple parameters such as this?  Can I use "exclude" somehow to do this?

               

              Thanks for all your help!

              • 4. Re: Using datediff to calculate total age based on multiple timestamps
                Wesley Magee

                Those use cases are more properly handled by IF/THEN type statements or filters. A LOD calculation allows you to aggregate measures at different level of granularity than are included in your view.

                 

                For example, if I had a map of 50 states had SUM(Sales) as my measure, it would always show the aggregation at the state level. If I wanted to have each state always show the total for the Country (maybe in the ToolTip), then I would fix by LOD calculation to Country. This means that the SUM(Sales) will always be calculated at the Country level no matter what is in my view (cities, ZIP codes).

                 

                Here's a good whitepaper that Tableau put out on LOD calculations (Understanding Level of Detail (LOD) Expressions with Tableau )