Rangesum with multiple measures

[tyember1]

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.

[Kashyap_R]

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

[jyothish8807]

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

[sunny_talwar]

Try this may be

Aggr(
  RangeSum(Above(
  Sum(DISTINCT QtyonHandINLOC)
  +Sum(Aggr(ActualQty, BatchNo))
  -Sum(Aggr(Qtyord, Ordno, Itemkey)), 0, RowNo()))
, [Item Description], [Location])

[tyember1]

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.

[sunny_talwar]

Sorry, but I am confused. Is your issue resolved now?

[tyember1]

Not yet my good man. Just trying a different avenue. attached is the end result that I am trying achieve (from Excel).

[sunny_talwar]

Would you be able to share you qvf file to look at what you have and try out few things?

[tyember1]

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

[tyember1 · Accepted solution]

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.