
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Rangesum with multiple measures
Hi everyone,
I am attempting to get a range sum by location (dimension). The TOTAL range sum works well, but I cannot seem to break it down by location. The range sum consists of multiple measures. below is my calculation. I have tried throwing in an aggr() function by location after the above as well as before the range sum, but no luck. I have also separated it out into three different range sum calculations but nothing changes. the last column in the attached image (Qty on Hand) is where the calculation in question is.
RangeSum(above(
Sum(distinct QtyonHandINLOC)
+sum(aggr(ActualQty,BatchNo))
-sum(aggr(Qtyord,Ordno,Itemkey)),0,RowNo()))
results in:
RangeSum(above(TOTAL
Sum(distinct QtyonHandINLOC)
+sum(aggr(ActualQty,BatchNo))
-sum(aggr(Qtyord,Ordno,Itemkey)),0,RowNo(TOTAL)))
results in:
but the end goal is to have the range sum by location. so we would have 3524 in the 2nd row and 771 for the third row.
Thanks for your help.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
My date field was causing a ruckus in my calculation, not sure why. I removed the date field and changed my expression to: Rangesum(above( aggr(above(total sum(distinct FinalQty),0,RowNo(total)),Reqdate,Ordno,Itemkey,Location),0,rowno(total)))
And now the field calculates correctly.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
Try sorting the table according to a location means to bring the location to the top in the sorting area.
hope it works,
Thanks
Kashyap.R


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
May be this:
RangeSum(above(TOTAL
aggr(Sum(distinct QtyonHandINLOC),Location)
+aggr(sum(ActualQty),BatchNo,Location))
-aggr(sum(Qtyord),Ordno,Itemkey,Location)),0,RowNo(TOTAL)))
Br,
KC
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this may be
Aggr(
RangeSum(Above(
Sum(DISTINCT QtyonHandINLOC)
+Sum(Aggr(ActualQty, BatchNo))
-Sum(Aggr(Qtyord, Ordno, Itemkey)), 0, RowNo()))
, [Item Description], [Location])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for all the helpful suggestions everyone! I have it sorted first by location then by target ship date.
The
"RangeSum(above(TOTAL
aggr(Sum(distinct QtyonHandINLOC),Location)
+aggr(sum(ActualQty),BatchNo,Location))
-aggr(sum(Qtyord),Ordno,Itemkey,Location)),0,RowNo(TOTAL)))"
formula ends up with incorrect values for a record and counts the rest 1,2,3 etc.
This formula
Aggr( RangeSum(Above( Sum(DISTINCT QtyonHandINLOC) +Sum(Aggr(ActualQty, BatchNo)) -Sum(Aggr(Qtyord, Ordno, Itemkey)), 0, RowNo())) , [Item Description], [Location])
sums up each location to one specific row entry and includes the previous location's qty in the new location's calculation. see attached image. I have resulted in this a few different times with mixing the formula around. if I take out the Item Description from the aggr, it stays the same. I am going to trim down the table to only the required aggr for the information. I am going to build out a dimension in the script to aggr on as I do not see a good way to make this work with the current dimensions.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, but I am confused. Is your issue resolved now?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not yet my good man. Just trying a different avenue. attached is the end result that I am trying achieve (from Excel).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Would you be able to share you qvf file to look at what you have and try out few things?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny,
Sorry for the late reply, was moved to a different project for awhile. I cannot provide a full qvf but here is an Excel snip of the data table I am working with. The "Qty on Hand" column is what should calculate a rolling sum aggregated by "Item Key & Desc". So when it is a new item, the rolling sum starts over for that specific item. the "Order & Production Qty" is the quantity field that the rolling sum is being calculated from. the current Qty on Hand function I am using is:
RangeSum(Above(total
Sum(aggr(QtyonHandINLOC,Itemkey,Ordno,Location))
+Sum(Aggr(ActualQty,Itemkey,Ordno,Location))
-Sum(Aggr(Qtyord,Itemkey,Ordno,Location)), 0, RowNo(total)))
Thanks for all the help you provide to the community!
-Tyler

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
My date field was causing a ruckus in my calculation, not sure why. I removed the date field and changed my expression to: Rangesum(above( aggr(above(total sum(distinct FinalQty),0,RowNo(total)),Reqdate,Ordno,Itemkey,Location),0,rowno(total)))
And now the field calculates correctly.
