Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raceberos
Partner - Contributor III
Partner - 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
sunny_talwar

May be this

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

View solution in original post

7 Replies
sunny_talwar

May be this

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

raceberos
Partner - Contributor III
Partner - Contributor III
Author

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

sunny_talwar

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
Partner - Contributor III
Partner - Contributor III
Author

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.

sunny_talwar

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
Partner - Contributor III
Partner - Contributor III
Author

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

BI_Rockstar_265
Contributor II
Contributor II

how to get % of cumulative orders?
rangesum giving correct results and i need each cumulative value/(max of cumulative value)