Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

raceberos
New Contributor III

rangesum with 2 dimensions

Good Day

i am wondering why my formula is not working properly. i just want to show my data in asset by branch per month,

when i add the month as a dimension everything seems fine,

and this is my formula

=rangesum( above( Sum({<GLHeader= {1}>} GLTOTAL ),0,rowno()))

Rangesum went wrong.JPG

but when i add the banchname as dimension my data went wrong.

Rangesum went wrong part 2.JPG

and i dont know why. can someone help me, any help will be appreciated

thanks

1 Solution

Accepted Solutions
MVP
MVP

Re: rangesum with 2 dimensions

May be this

=Aggr(RangeSum(Above(Sum({<GLHeader= {1}>} GLTOTAL), 0, RowNo())), Branch, Month)

6 Replies
MVP
MVP

Re: rangesum with 2 dimensions

May be this

=Aggr(RangeSum(Above(Sum({<GLHeader= {1}>} GLTOTAL), 0, RowNo())), Branch, Month)

raceberos
New Contributor III

Re: rangesum with 2 dimensions

thanks mate it works.. i really i appreciate your help.. do you have any reference on Aggr and RangeSum iam researching on it im looking for just a simple explanation.

thanks

MVP
MVP

Re: rangesum with 2 dimensions

The issue is that RangeSum(Above()) accumulate for the second dimension (unless you have specified TOTAL which is not what you needed because then it accumulates regardless of your 1st dimension). Now the problem was that you had Month as your 1st dimension and Branch as your second dimension.... so the accumulation was taking place for Branch rather than accumulating for Months for a single branch. In order to fix this, we use Aggr() function where we supplied Branch as the first dimension and Month as the second dimension

=Aggr(RangeSum(Above(Sum({<GLHeader= {1}>} GLTOTAL), 0, RowNo())), Branch, Month)

Now there might have been a case where Month was not correctly sorted in the load script which can then be solved as mentioned here

The sortable Aggr function is finally here!

raceberos
New Contributor III

Re: rangesum with 2 dimensions

Hi good day mate... sorry to ask again... the formula you refer to me works fine i already created multiple financial KPI's but after i check every details i notice something is wrong

the first chart is fine

Working Error.jpg

but after i select a specific month it does not aggregate from jan - up to the selected date.

Working Error part 2.jpg

i hope you can help me again, and your help is really really appreciated.

and sorry for keep on asking. i just don't have anybody to ask for help personally

thanks.

MVP
MVP

Re: rangesum with 2 dimensions

Did you select Branch or did it accidentally selected Month? May be try this

=Aggr(RangeSum(Above(Sum({<GLHeader= {1}, Month>} GLTOTAL), 0, RowNo())), Branch, Month)

raceberos
New Contributor III

Re: rangesum with 2 dimensions

it works mate.. now i have an additional idea on how rangesum works... thanks alot