Rolling sum - easy in principle - but something is wrong - plz help

[datanibbler]

Hi,

I want to create a rolling sum in a table.

In principle, I think that is quite easy: I sort my table by item_no (that is my key) ascending (in a RESIDENT LOAD) and,

out of three fields, I calculate the sum that I need for that individual record - so far, everything is fine.

=> Then I use the PREVIOUS() function to decide if the item_no in the current record is still the same as in the last record.

If it is, I just add that sum to the one from the last record.

If it is not, I reset my rolling sum and just copy the sum I have for that individual record.

<=> Something must be wrong there, it doesn't work: I do not have to aggregate or something, do I?

Can you help me out here?

Thanks a lot!

Best regards,

DataNibbler

[marcus_sommer]

Hi DataNibbler,

please post your (maybe simplified) script.

- Marcus

[Gysbert_Wassenaar]

You need to use Peek to retrieve the rolling sum of the previous record.

If(Previous(Key)=Key, rangesum(Value, peek(RollingSum)), Value) as RollingSum

In other words if-previous-then-peek

[datanibbler]

Hi Marcus,

I'm just seeing something - when I use a regular diagram, not a tablebox, to view the fields, I see that one of the three fields I am using to build my sum is often not filled.

Oops - something must have been wrong with my data - I reloaded from the database just now, and my app just went from not_working  back to working ...very strange.

Something is still wrong, though - maybe I have to use a TrIM() - it seems that sometimes the item_no is not properly recognized and my PREVIOUS() misfires ...

I still attach the script so you can have a look at it.

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi Gysbert,

that is an idea - but why can't I use PREVIOUS() for the rolling sum, too?

P.S.: Sorry, I forgot to attach my script. I'm doing so now.

[marcus_sommer]

Here is an excellent explanation what are the differences of peek and previous: Peek() or Previous() ?

- Marcus

[tresesco]

Try:

IF(PREVIOUS(PACKAGE_ITEMS.ITEM_NUMBER)<>PACKAGE_ITEMS.ITEM_NUMBER, noch_zu_packen_ind, RangeSum(Peek(noch_zu_packen_ges), noch_zu_packen_ind))) as noch_zu_packen_ges:

[datanibbler]

Hi,

there must still be some bug:

Within some groups of records for one and the same item_no, my rolling_sum is now always continued for a while - then it is suddenly reset to 0 without a change in item_no - well, there must be some difference in those cases causing the rolling_sum to be reset, but I cannot see it. The LEN() is the same, so TRIM() would not help ...

[Gysbert_Wassenaar]

Previous looks back into the source table where you only have the original Value values. You need to use peek to look in the target table where your rolling sum values exist.

[datanibbler]

Yes, I am now using PEEK() in the rolling sum like you proposed - the PREVIOUS() is only in the part where I look at the item_no in the record above, that is unchanged from the source table - well, as it is obviously not working the way it is, I might as well try PEEK() in that part, too ...