Aggr monthly balances per year

[pauldamen]

All,

I have monthly balances with a starting balance in January, the rest of the year are changes to this balance. So in order to show correct numbers I need to show the balances per month aggr for that year. The table looks like this (in the 3rd kolom I have show what I need):

jan-15      10.000          10.000

feb-15          500           10.500

mrt-15         -500           10.000

apr-15          750            10.750

may-15         500          11.250

jun-15       -1.250          10.000

jul-15          500              10.500

aug-15         500             11.000

sep-15          500            11.500

oct-15        -1.500          10.000

nov-15      250               10.250

dec-15       250                10.500

jan-16     10.000              10.000

I tried this formula: sum(Aggr(sum(Amount), Year)). But this shows me numbers only in january not the rest of the months. How can I achieve what i want?

Regards, Paul

[marcus_sommer]

Try it with: rangesum(sum(Amount), 0, rowno())

- Marcus

[pauldamen]

What would I get for outcome? My table starts on september 2013, so that should aggr for 4 months and then start in january of each year again till the end of the year.

If I use your suggestion this still gives me different numbers since the january start is not taken into account?

[jonathandienst]

I assume you mean in a table/chart in the front end. Use an expression like:

=RangeSum(Above(Sum(Balance), 0, RowNo()))

Replace "Sum(Balance)" with the expression for balance, or with Column(1) if balance is in the first expression. Use in a table or chart witha year/month dimension as in your post,

[jonathandienst]

>>What would I get for outcome? My table starts on september 2013, so that should aggr for 4 months and then start in january of each year again till the end of the year.

You would be best off doing this in script rather than doing it in the front end. But if you can't do that, then use Month as a dimension and select the year in a list box or set expression, and it will always start the accumulation from the first month of the year.

[christophebrault]

Hi,

Marcus, I think with RangeSum you must use Above like this :

RangeSum(Above(sum(Amount), 0, rowno()))

[pauldamen]

What do you mean with sum(balance)?

With the above formula I just get my booking every month, it is not accumulating

[pauldamen]

Hi Christophe,

With adding the Above it worked!

But how can I limit the formula per year? So I starts off in january not taking the previous year(s) in account?

[sunny_talwar]

Try this:

=RangeSum(Above(Sum(Amount), 0, RowNo()))

or if you have multiple dimensions and you want full accumulation on all your dimensions

=RangeSum(Above(TOTAL Sum(Amount), 0, RowNo(TOTAL)))

[jonathandienst]

>>What do you mean with sum(balance)?

Well you haven't posted an example, or any details on your existing expression, that's why I said

"Replace "Sum(Balance)" with the expression for balance"