1 Reply Latest reply on Jun 28, 2016 4:55 AM by Yuriy Fal

    Date Data Verification Matrix Viz

    Brian Delehanty

      I have an idea visualized in my head that I cannot for the life of me figure out how to build. My data set looks has several date columns per record that should fall in a particular order every time (i.e. a submit date cannot be after an approval date). As part of a data cleanup initiative to identify "fat fingering" dates, I want to use tableau to quickly identify records where dates are out of sequence. The particular way I'd like to show this is with a matrix with all of the date types on the columns on both the horizontal and vertical axis like so:

       

          

      Submit DateApproval DateComplete Date
      Submit Date
      Approval Date
      Complete Date

       

      Then in each cell of the matrix count the number of loans that are out of order. For instance, Approval Date and Complete Date should always be after sub date so in the Submit Date Row I would count records where Approval Date or Complete Date is less than Submit Date. For Approval Date, it should be between Submit Date and Complete Date so count records with a Submit Date greater than Approval Date and Complete Date less than Approval Date.

       

      Any ideas? Thanks as always!

        • 1. Re: Date Data Verification Matrix Viz
          Yuriy Fal

          Hi Brian,

           

          You could create a number of Row-Level calculations like this:

           

          DATEDIFF( 'day', [Submit Date], [Approval Date] ) < 0

           

          which means that [Approval Date] is less than [Submit Date] --

          for that particular row in your dataset. If the result is TRUE,

          than you've caught a suspicious record.

           

          Hope this could help.

           

          Yours,

          Yuri