Accepted Solutions (0)
Answers (8)
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
| User | Count |
|---|---|
| 88 | |
| 10 | |
| 10 | |
| 9 | |
| 7 | |
| 7 | |
| 6 | |
| 5 | |
| 4 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.