• Replies 12 replies
  • Subscribers 339 subscribers
  • Views 138 views
  • Users 0 members are here
Options
Related

Identify batches where create date, closing date, and posting date do not line up

Michael Dorsey
$organization

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

Parents
  • $organization

    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 )
Reply
  • $organization

    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 )
Children