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.

  • Hi Veronica,

    Can you share a screenshot, with blurred values if not otherwise possible? Am currently on annual leave but will have a look once I am back. We should be able to filter out the unwanted row.

    Kind regards,

    Tom

  • Get RSS Feed

8 Answers

  • Mar 13 at 04:59 PM

    I have webi and desktop both available to use.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 14 at 09:36 PM

    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

  • Mar 15 at 04:58 PM

    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

  • Mar 15 at 07:34 PM

    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

  • Mar 17 at 05:55 AM

    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

  • Mar 19 at 08:19 PM

    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

  • Mar 26 at 05:11 PM

    Hi Veronica,

    Apologies for the radio silence on this.

    You may have found a solution yourself but in case you didn’t here is how you can get to your desired result.

    We need to run three queries instead of just the two as otherwise we have difficulties in synchronising the data correctly:

    Query 1 (Acct Start): [Account Number], [Start Date], [Initial Amount]

    Query 2 (Acct End): [Account Number], [End Date]

    Query 3 (Acct Payment): [Account Number], [Date], [Amount]

    Once you have run the three queries, merge the Account Numbers and Dates as follows:

    1. Merge all [Account Number] dimensions into one [Account Number] merged dimension, making [Account Number (Acct Start)] the source dimension

    2. Merge [Date], [End Date] and [Start Date] into one [Date] merged dimension, making [Date (Acct Payment)] the source dimension

    In the next steps, create four measure variables:

    1. [Full Amount] =[Amount]+[Initial Amount]

    This simply merges the two measures from our queries 1 and 3 into one measure so we can use RunningSum() on just one measure variable.

    2. [Running Sum] =NoFilter(Previous(RunningSum([Full Amount];([Account Number]));([Account Number]);1))

    We are working with the Previous() function to ensure we allocate the correct values in each row according to their begin and end dates (see next variable). The NoFilter() function is important as you will need to apply a table filter later on.

    3. [Date From] =NoFilter(If(IsNull(Previous([Amount];([Account Number]);1)) And IsNull(Previous([Initial Amount];([Account Number]);1))) Then ([Start Date]) Else Previous([Date];([Account Number]);1))

    We need to be a little inventive to pair the correct begin dates (Date From) with the relevant end dates (Date To, i.e. Date). In order to achieve this, we do a row-by-row validation against amount and initial amount and allocate the start date from Query 1 where there are no values for both of them, or the Previous([Date]) value where there are values for either Amount or Initial Amount. Again, we need the NoFilter() function as a wrapper.

    4. [Hide Me Filter] =[Date] > Max([Start Date]) In([Account Number])

    This variable acts as a helper variable to dispose of those rows which contain transaction dates smaller than the start date in each account number.

    Now that you have created the four variables you can move on to create your report:

    1. Open a new report tab and pull the following objects onto the report canvas:

    [Acct Start].[Account Number], [Date From], [Date], and [Running Sum]

    As you can see from the screenshot above, we have begin and end dates as we want them. But we still need to dispose of the lines framed in red:

    To do this, you now apply a filter to your table block: [Hide Me Filter] Equal To 1

    Finally, insert a section, using the [Account Number] merged object and adjust the section and/or table layout to your needs.

    If you are retrieving hundreds of account numbers I would also consider creating an Input Control on [Account Number] for quicker navigation in preference to the report tab. In my opinion, using both Input Control and Report Map in combination works best.

    Let me know how you got on.

    Kind regards,

    Tom


    12629431a.png (21.5 kB)
    12629431b.png (2.1 kB)
    12629431c.png (64.5 kB)
    12629431d.png (150.4 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Tom - apologies for the delayed response. This works with some limitations. When my Account Payment file has a two transactions before the Start Date in the Acct Start file, I get an unwanted first line of line, as I now have a full line of data (Start and End). Any suggestions other than removing the transactions in the Account Payment file (which is possible, but not preferable, as this is a process I hope to perform on a scheduled, on-going basis).

  • May 09 at 12:03 PM

    Hi Veronica,

    Further to your comment dated Apr 23 at 09:30 PM and my response dated Apr 25 I am not quite sure I understand the limitation correctly. If you look at the sample data set in the explanation/description I provided on Mar 26 you will notice that Account Number 22222-2 has got two transactions before the Start Date with - in my view - no additional line. Maybe you can provide a screenshot to highlight the issue?

    Kind regards,

    Tom

    Add comment
    10|10000 characters needed characters exceeded