We have this requirement of running sum for billed amount.
There are different line items for each bill, and there are many bills in a day.
I have a report which shows all the bills in a day with a break on the bill number. For each bill we want the running sum. But sadly I tried all options like Runningsum(billed_amt) For Each Bill number… but the running sum is not reset to zero after each bill, it looks like it is giving the running sum for the whole day.
Any inputs how we can get running sum for each bill ??
I have a report which shows all the bills in a day with a break on the bill number. For each bill we want the running sum. But sadly I tried all options like Runningsum(billed_amt) For Each Bill number…
but the running sum is not reset to zero after each bill, it looks like it is giving the running sum for the whole day. >
try using the syntax
=RunningAverage(<billed_amt>;)
I have a report which shows all the bills in a day with a break on the bill number. For each bill we want the running sum. But sadly I tried all options like Runningsum(billed_amt) For Each Bill number…
but the running sum is not reset to zero after each bill, it looks like it is giving the running sum for the whole day. >
try using the syntax
=RunningAverage(<billed_amt>;)
Correct me if I’m wrong, but didn’t you say you wanted the runningSUM? How is runningaverage going to give you the same results? I have a report with breaks using runningsum and it resets and calculates as it should. Are you sure you have all the arguments in the formula that you need?
Shelley
<
I have a report which shows all the bills in a day with a break on the bill number. For each bill we want the running sum. But sadly I tried all options like Runningsum(billed_amt) For Each Bill number…
but the running sum is not reset to zero after each bill, it looks like it is giving the running sum for the whole day. >
try using the syntax
=RunningAverage(<billed_amt>;)
I am trying to get the RunningSum on one column. I have columns Device Code & Invoice Quantity I want to get the running total of Invoice Quantity
for each individual Device Code. When I try to use the RunningSum function on the device code , the running sum
continues from one device code to other next device code. I tried doing this by having break / Master on device code but it doesn’t work.
Need help.
Vikas Hawaldar
IBM Global Services /
Lucent Technologies O, ME
610.712.8479
I am trying to get the RunningSum on one column. I have columns Device Code & Invoice Quantity I want to get the running total of Invoice Quantity for each > individual Device Code. When I try to use the RunningSum function on the device code, the running sum continues from one device code to other next device code.
Try using the reset operator – ;
Your example might look like this:
=RunningSum(;)
This will reset the running sum every time the Device Code changes.
I am trying to get the RunningSum on one column. I have columns Device Code & Invoice Quantity I want to get the running total of Invoice Quantity for each > individual Device Code. When I try to use the RunningSum function on the device code, the running sum continues from one device code to other next device code.
If a filter is added on the formula should be
=NoFilter(RunningSum(;))
otherwise the running sum will be incorrectly computed.
I 'm trying to make a report on day-wise closing stock of all products in different warehouses. The objects which I 'm selecting from the query panel are Month, Date, Product, Wh Name & Closing Stock. In the back-end database table a row will be created for a product & warehouse combination only when there is a change in stock position. (i.e. row will not be created for non moving stocks for that many days - this was done to optimise the table size). When I sum the closing stock to obtain day-wise stock I 'm not getting the correct figure because the query doesn’t find rows for those prodct-warehouse combination which has no stock movement on that date.
I 've solved the problem using stored procedures. Is there any other elegant solution?
One thing to try is using a correlated subquery to get the maximum date less than or equal to a particular date that you want to see the closing stock for. You can build the relationship right into your universe join. For instance, if you have a date table and a stock table and you want to see the sum of stock on a date, your generated SQL might look like:
SELECT DATE_TBL.DATE, SUM(STOCK_TBL.CLOSING_STOCK) FROM DATE_TBL, STOCK_TBL
WHERE STOCK_TBL.DATE = (SELECT MAX(A.DATE)
FROM STOCK_TBL A
WHERE A.PRODUCT = STOCK_TBL.PRODUCT
AND A.WAREHOUSE = STOCK_TBL.WAREHOUSE
AND A.DATE <= DATE_TBL.DATE)
GROUP BY DATE_TBL.DATE
For performance, an index should be on the STOCK_TBL with PRODUCT, WAREHOUSE, and DATE. DATE should be descending in the index assuming the most current dates are most frequently accessed.
I 'm trying to make a report on day-wise closing stock of all products in different warehouses. The objects which I 'm selecting from the query panel are Month, Date, Product, Wh Name & Closing Stock. In the back-end database table a row will be created for a product & warehouse combination only when there is a change in stock position. (i.e. row will not be created for non moving stocks for that many days - this was done to optimise the table size). When I sum the closing stock to obtain day-wise stock I 'm not getting the correct figure because the query doesn’t find rows for those prodct-warehouse combination which has no stock movement on that date.
I want to get a runningsum calculation to reset depending on the master(s) = and/or drill filter(s) but I can’t seem to find a way to do it. =3Drunning= sum(;in body) doesn’t work nor does any other variation that I have = tried. I am using BO 2.5.2.