Skip to Content

Running Sum Table Between Two Dates in Separate Query, Starting with Data from Separate Query

Query 1 gives start and end date parameters and an initial value
Query 2 holds transaction data by date (date and amount).

Desired end result is a data table beginning with start date in Query 1 and initial value in Query 1. I need a line for each entry in Query 2 though the end date in Query 1.

For example, Query 1 is 3rd quarter 2018 (10/1/2018 through 12/31/2018). Initial value is 100,000

Query 2 has the following
10/15/2018 50,000

11/7/2018 -10,000

12/6/2018 5,000

Required End Result Table
10 /1/2018 - 10/15/2018 100,000

10/15/2018 - 11/7/2018 150,000

11/7/2018 - 12/6/2018 140,000

12/6/2018 - 12/31/2018 145,000

Any assistance would be greatly appreciated. Thank you.



Add comment
10|10000 characters needed characters exceeded

  • It would help community members if you specify what product you are actually using, I have changed your tag to Webi as it sounded closest to what you described, so if it is not webi - please update.

  • Get RSS Feed

6 Answers

  • Mar 13 at 04:59 PM

    I have webi and desktop both available to use.

    Add comment
    10|10000 characters needed characters exceeded

  • 6 days ago

    Hi Veronica,

    You can achieve this by following these steps:

    1. Merge your Start Date (Query 1) with Transaction Date (Query 2) and rename the merged dimension [Merged_Date]
    2. Create a variable [End Date] with =(If(Quarter([Merged_Date])=1) Then "03/31/"+""+FormatNumber(Year([Transaction_Date]);"0000")ElseIf(Quarter([Merged_Date])=2) Then "06/30/"+""+FormatNumber(Year([Transaction_Date]);"0000")ElseIf(Quarter([Merged_Date])=3) Then "09/30/"+""+FormatNumber(Year([Transaction_Date]);"0000")ElseIf(Quarter([Merged_Date])=4) Then "12/31/"+""+FormatNumber(Year([Transaction_Date]);"0000"))
    3. Create another variable [Date Range] with =If(RowIndex()= (Max(RowIndex()) In Report)) Then [Transaction_Date]+" - "+[End Date] Else [Merged_Date]+" - "+RelativeValue([Start_Date];([Merged_Date]);1)
    4. Create a third variable [Running Sum] with =If(RowIndex()=0) Then [Initial_Value] Else(RunningSum([Amount]+Previous([Initial_Value])))
    5. Finally, create a table with [Merged_Date], [Date Range] and [Running Sum]. Then hide [Merged_Date]

    The [End Date] variable forces the relevant quarter end date in combination with the [Date Range] variable

    Please let me know whether this worked for you.

    Kind regards,

    Tom

    Add comment
    10|10000 characters needed characters exceeded

  • 5 days ago

    Thank you so much. I have not been clear enough in my example.

    Q1 is not traditional quarters. It does have a start date and end date, but these vary from account to account.

    Q2 has data with dates before and after the "Start" and "End" dates.

    I am getting a #COMPUTATION error and I am getting lines outside of my date range.

    Also, I do not need to have the "From Date" and "To Date" in one field. It is OK if I do that but not necessary. I will need to perform calculations using "Days Between" those two fields.

    Again, thank you for any tips. I really appreciate it.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Veronica,

      Do your account specific quarters at least start and end within the traditional quarter definition, e.g. 01/04/2019 - 03/23/2019, or could they start/begin outside the normal ranges?

      Could you possibly upload/attach a larger sample (csv or txt would be fine) that includes all possibilities you have?

      Thanks,

      Tom

  • 5 days ago

    Some accounts are a 90 day date-range and some are a 30-day date range (billed on a monthly or quarterly basis). End goal is to schedule this report and drop statements when the "end date" from Q1 changes from the previous day. I really should not have transactions after my end date, but I do know because I am working with a snapshot of a prior month-end. We can assume I won't have those going forward.

    My end date for a quarter (or cut-off period) could be a few days after a month end but not in the middle of the month

    Some accounts will have no transactions between the start and end date. Some accounts will have many transactions.

    Created a sample file.

    bic-example-account.txt

    bic-example-payment.txt

    Add comment
    10|10000 characters needed characters exceeded

  • 4 days ago

    My end date should always the prior business date, as that is the date the statement should cut off.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Veronica,

      I am still looking into this but the new account dimension does prove to be an extra challenge...

      Looking at your samples, I have a few more questions:

      • Acct 11111-1 has no transactions. Do you expect a result to be returned for this report, i.e. just a one-liner with the initial amount?
      • Acct 22222-2 has transactions before the start date. Do you expect your list to be sorted by (merged) date or begin with the actual start date and then sorted by transaction date?
      • You mention your ultimate goal is "to schedule this report and drop statements when the "end date" from Q1 changes from the previous day."
      • Do you want to run one report for all accounts, or do you intend to run them separately. If one report, would you consider a sectioned report, i.e. one section per account?
      • What database is your source system?

      Kind regards,

      Tom

  • yesterday

    Tom,

    If there are no transactions between the start date, I need one line in my table with the start date and end date. So for 11111-1 I would need a line with 10/1/2018 - 1/1/2019.

    For 22222-2, I would want the first line to start with the date from Q1 (the account). I do not want or need to see any transactions before that date, but I also do not want to have to limit my data coming in from Q2.

    22222-2 desired end result in example would be
    12/1/2018 - 12/22/2018
    12/22/2018 - 1/1/2019

    I need to be able to perform calculations on the each line, using DaysBetween the two dates (multiplying the running sum times rate times the number of days divided by a basis code -- an interest calculation).

    Data source is a vendor who houses our data. The payment information is first run as a separate summed up to a date level and then brought back in as a Hold File.

    My end report is a .pdf that is sectioned by account number.


    Add comment
    10|10000 characters needed characters exceeded