Hello all,
I'm wondering if anyone has some SQL or some other method that helps them identify batches where the create, closing, and posting dates are different from one another. To be clear, I want to get a count of any batches where at least one of the three dates is not the same in a given time frame (i.e., 6/1/23-6/30/23). We're using Tessitura Merchant Services, and we've been noticing a lot of the reports from Adyen and Tessitura are at odds with one another. My suspicion is that this is because of batches opened on one date, and closed and/or posted on another date. Or if there is some easy, out-of-the-box way to get this, let me know what that is.
Thanks in advance,
Michael Dorsey
CRM Systems Manager
Spoleto Festival USA
You might consider Analytics as well. All three dates are available in the Finance cube.
A flag formula that converts each date to an integer, compares them, and returns a 1 when any are not the same as the rest, could work as a filter to see only those that are not aligned.
So a Finance Pivot widget with a filter on Batch Create date Month = 2023-06. Perhaps also a filter on Post Date > 1900-01-01 (the date for not-posted batches)
On rows, Batch ID, Days in Batch Create Date, Days in Batch Close Date, and Days in Post Date, and a value like this then filtered to only show rows where results = 1:
IF (MAX( [Created Calendar Year] * 1000 + [Created Calendar Day of Year] ) <> MAX( [Closed Calendar Year] * 1000 + [Closed Calendar Day of Year] )OR MAX( [Created Calendar Year] * 1000 + [Created Calendar Day of Year] ) <> MAX( [Posted Calendar Year] * 1000 + [Posted Calendar Day of Year] )OR MAX( [Closed Calendar Year] * 1000 + [Closed Calendar Day of Year] ) <> MAX( [Posted Calendar Year] * 1000 + [Posted Calendar Day of Year] ), 1 , 0 )
Hi Chris, I did the above and I'm only getting a "1" if all 3 dates don't match... how would I change it up to flag it if 2 dates are the same but 1 is different? Thanks! Nicki
Silly me... those ANDs were supposed to be ORs. Updating my original post.
Thank you!! Works beautifully.