topic Re: Rangesum with multiple measures in App Development

Rangesum with multiple measures

tyember1 — Sat, 16 Nov 2024 04:23:16 GMT

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.

Re: Rangesum with multiple measures

Kashyap_R — Tue, 15 Oct 2019 08:41:59 GMT

Try sorting the table according to a location means to bring the location to the top in the sorting area.

hope it works,

Thanks

Re: Rangesum with multiple measures

jyothish8807 — Tue, 15 Oct 2019 09:03:49 GMT

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,

Re: Rangesum with multiple measures

sunny_talwar — Tue, 15 Oct 2019 11:55:19 GMT

Try this may be

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

Re: Rangesum with multiple measures

tyember1 — Tue, 15 Oct 2019 19:08:20 GMT

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.

Re: Rangesum with multiple measures

sunny_talwar — Tue, 15 Oct 2019 19:53:50 GMT

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

Re: Rangesum with multiple measures

tyember1 — Tue, 15 Oct 2019 19:58:03 GMT

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

Re: Rangesum with multiple measures

sunny_talwar — Wed, 16 Oct 2019 11:05:37 GMT

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

Re: Rangesum with multiple measures

tyember1 — Fri, 15 Nov 2019 19:30:56 GMT

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

Re: Rangesum with multiple measures

tyember1 — Mon, 18 Nov 2019 15:57:15 GMT

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.