
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Rangesum Above with Multiple Dimensions
Hi All,
I have the below pivot table
The balance is the previous weeks balance plus the previous weeks revenues which I get using the expression
RangeSum(
Sum(TOTAL {<[Weekstart]>} Balance),
RangeSum(Before(Sum({<[ Weekstart]>}UnsettledSell)
, 1, ColumnNo())))
I then have appended this expression to the following for use in a line chart and it works great.
RangeSum(
Sum(TOTAL {<[Weekstart]>} Balance),
RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)
, 1, RowNo())))
The problem I am having is I want to add another dimension 'Company' to the line chart it ruins the expression and gives me the below.
I believe it has to do with the above function and multiple dimensions but I am unsure on how to solve.
Thanks!!
Mark
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you sure you used this?
Aggr(
RangeSum(
Sum(TOTAL <Company> {<[Weekstart]>} Balance),
RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)
, 1, RowNo())))
, Company, (Weekstart, (NUMERIC)))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be try this
Aggr(
RangeSum(
Sum(TOTAL <Company> {<[Weekstart]>} Balance),
RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)
, 1, RowNo())))
, Company, Weekstart)
or this
Aggr(
RangeSum(
Sum(TOTAL {<[Weekstart]>} Balance),
RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)
, 1, RowNo())))
, Company, Weekstart)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not quite working, the balances seem to be applied to the incorrect weeks.
The pivot table shows the desired results and the straight table shows what I am getting

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How about this?
Aggr(
RangeSum(
Sum(TOTAL <Company> {<[Weekstart]>} Balance),
RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)
, 1, RowNo())))
, Company, (Weekstart, (NUMERIC)))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Closer I think! That works great when I have one company selected, however if I select a second company the starting balance for each company becomes the sum of their two starting balances. Their revenues are then applied appropriately in subsequent weeks but the starting balance is the sum of the two companies.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you sure you used this?
Aggr(
RangeSum(
Sum(TOTAL <Company> {<[Weekstart]>} Balance),
RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)
, 1, RowNo())))
, Company, (Weekstart, (NUMERIC)))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ah I was not, it works great thank you!!
