5 Replies Latest reply: Jun 1, 2015 10:39 AM by kushal chawda

# Bank account graph not OK

Hi All,

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

Lets say my data within my database is:

Bank account numberDateAmount
111111112014-01-01+1000
111111112014-06-01+400
111111112014-07-01-300
111111112015-02-01+600
222222222014-01-01+2000
222222222015-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.

• ###### Re: Bank account graph not OK

This is what you are looking for?

I have attached the qvw file

• ###### Re: Bank account graph not OK

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.

• ###### Re: Bank account graph not OK

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

• ###### Re: Bank account graph not OK

I hope this helps.

• ###### Re: Bank account graph not OK

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.