Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
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()))

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

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

thanks

1 Solution

Accepted Solutions
MVP

May be this

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

7 Replies
MVP

May be this

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

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

MVP

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!

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

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

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

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)

Partner - Contributor III
Author

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

Contributor II

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

Tags