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 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!
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.