1 Reply Latest reply on Aug 9, 2016 6:54 AM by Benjamin Greene

    Calculating Total Average Days Elapsed between Transaction and MAX Entry Date (LOD Question)

    Brian Scelzo

      Hi everyone, I have a bit of a head-scratcher here.  I have a data set of travel expenses that I am working with.  When a purchase is posted to the bank, the Post Date is populated.  If the employee hasn't entered the associated expense report, we accrue the expenses via journal entry, J/E month field.  IF the expense hasn't been entered after the first J/E month, it is re-accrued in the next month. 

       

      So it is possible for a Transaction Number to have a single post date and multiple J/E dates.  What I need is to show the Employee Name and the current Average Elapsed Days.  This calculation should the days elapsed for the Post Date and the max J/E date associated with the transaction number.  Then a total average. 

       

      I've included an excel doc with dummy data, that shows the calculation and solution as well as a preferred view for tableau.  I've tried using LOD calculations to arrive at this answer, but that won't let me arrive at the preferred view I'm looking for.  Any help would be greatly appreciated!

       

      Thanks!

      Brian

        • 1. Re: Calculating Total Average Days Elapsed between Transaction and MAX Entry Date (LOD Question)
          Benjamin Greene

          If I am understanding your question correctly, the LOD part of it should be something like:

          {FIXED [Transaction #] : MAX([J/E Date])}

          This will create a new column that will give the same value to all rows that share a transaction number. This value will be the latest J/E date within that transaction number. Factoring in the days elapsed part should give us something like:

          DATEDIFF('day', [Post Date], {FIXED [Transaction #] : MAX([J/E Date])})

          Now, in your excel sheet, you have null values for all rows except that first row for each transaction number. If you want to code in those conditional null values, you could try something like:

          IF [J/E Date] = {FIXED [Transaction #] : MIN([J/E Date])}

          THEN DATEDIFF('day', [Post Date], {FIXED [Transaction #] : MAX([J/E Date])})

          ELSE NULL

          END

          This will only perform the days elapsed calculation for the row whose J/E date is equal to the minimum J/E date within that transaction number.

          Finally, in the view, just pull the CH FULL NAME to rows and the above calculated field to label (set aggregation to AVG) and you should get the desired result...I think...

           

          EDIT: Fixing a couple formula typos