Bank account graph not OK

[heij1421]

Hi All,

I want to create a graph from my bank transactions over time

Lets say my data within my database is:

Bank account number	Date	Amount
11111111	2014-01-01	+1000
11111111	2014-06-01	+400
11111111	2014-07-01	-300
11111111	2015-02-01	+600
22222222	2014-01-01	+2000
22222222	2015-01-01	+400

When I calculate this on Bank account 11111111 the total amount on my account today is (+1000+400-300+600) = 1700 EUR

and the amount on bank account 22222222 is (+2000 + 400) = 2400 EUR

Now I want to generate a Line Graph with a line over time of my bank balance.

So my dimensions are [Date] and [Bank account number] and my expression is SUM(Amount). And in the expression tab of my graph I set the bullet to 'Full Accumulation'. So far so good.

Now I have 3 questions

1.

The graph is OK, but when I select 2015 (a master calendar is linked to my date), the graph says my balance is +600 on bank account 111111 and +400 on bank account 22222222. I understand why but the balance is not OK. It should also include all other transaction lines of the previous dates. The simple solution would be to change the expression to SUM({1} Amount).

2. When I change the expression to SUM({1} Amount) I cannot zoom in to the graph of only 2015.

3. Next to the 2 lines in my graph of the separate bank accounts, I also want to total line for both the bank accounts.  How do I do that?

How can I fix these issues?

note: this example is simplified. I have multiple bank accounts and thousands of lines in my database.

[Kushal_Chawda · Accepted solution]

This is what you are looking for?

I have attached the qvw file

[heij1421]

Thanks for your solution. I see you have solved in in the script. I didn't think of that.Thanks.

One more question for now: when I have 2 lines on the same date, that the SUM(balance) doesn't work.

Let's say I add a line:

11111111,

2014-01-01,

200

Then my balance of 2014-01-01 is 1000+200+1000+200. How can I solve that? I also have an ID on my table. I probably can use that one. But per unique date I want a value in my graph.

[Gysbert_Wassenaar]

Perhaps this document helps with getting the balance amounts right: Calculating rolling n-period totals, averages or other aggregations‌. The AsOf Table Examples.qvw example app contains a RollingToDate option that looks like what you could use.

[Kushal_Chawda]

In that case you can use the expression in chart as : Sum( distinct Balance)

[Kushal_Chawda]

I hope this helps.