topic Re: Aggr monthly balances per year in QlikView

Aggr monthly balances per year

pauldamen — Thu, 28 Apr 2016 11:58:50 GMT

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

Re: Aggr monthly balances per year

marcus_sommer — Thu, 28 Apr 2016 12:04:12 GMT

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

- Marcus

Re: Aggr monthly balances per year

pauldamen — Thu, 28 Apr 2016 12:07:18 GMT

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?

Re: Aggr monthly balances per year

jonathandienst — Thu, 28 Apr 2016 12:11:59 GMT

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,

Re: Aggr monthly balances per year

jonathandienst — Thu, 28 Apr 2016 12:14:33 GMT

>>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.

Re: Aggr monthly balances per year

christophebrault — Thu, 28 Apr 2016 12:16:01 GMT

Hi,

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

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

Re: Aggr monthly balances per year

pauldamen — Thu, 28 Apr 2016 12:16:51 GMT

What do you mean with sum(balance)?

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

Re: Aggr monthly balances per year

pauldamen — Thu, 28 Apr 2016 12:19:02 GMT

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?

Re: Aggr monthly balances per year

sunny_talwar — Thu, 28 Apr 2016 12:19:23 GMT

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)))

Re: Aggr monthly balances per year

jonathandienst — Thu, 28 Apr 2016 12:20:25 GMT

>>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"

Re: Aggr monthly balances per year

sunny_talwar — Thu, 28 Apr 2016 12:20:44 GMT

If you have a monthfield created in the script like this -> Month(DateField) as Month, you can try this:

RangeSum(Above(Sum(Amount), 0, Num(Month)))

Re: Aggr monthly balances per year

christophebrault — Thu, 28 Apr 2016 12:20:59 GMT

To calculate it in the script you can use something like this :

TABLE_TEMP :

LOAD Month

BalanceAmount

FROM YourSourceFile ... ;

TABLE :

LOAD Month

BalanceAmount,

BalanceAmount+peek(BalanceAmount) as AccumulatedBalanceAmount

Resident TABLE_TEMP ORDER BY Month;

drop table TABLE_TEMP ;

Re: Aggr monthly balances per year

pauldamen — Thu, 28 Apr 2016 12:31:03 GMT

With the num(month) it worked!

Re: Aggr monthly balances per year

sunny_talwar — Thu, 28 Apr 2016 12:33:02 GMT

Awesome

Re: Aggr monthly balances per year

pauldamen — Thu, 28 Apr 2016 12:38:40 GMT

Sunny question;

I moved my Month dimension from the left to the top so I have the months next to each other (it is a pivot table) but now the formula doesn't work anymore. What do I need to change?

Regards, Paul

Re: Aggr monthly balances per year

sunny_talwar — Thu, 28 Apr 2016 12:39:54 GMT

This:

RangeSum(Before(Sum(Amount), 0, Num(Month)))

or may be After (based on sorting)

RangeSum(After(Sum(Amount), 0, Num(Month)))