There is an offer to transfer the accumulation to the level of the script, creating it in a separate field.
[TRX Date, Debit- Credit
Date(Date#([TRX Date], 'DD/MM/YYYY')) as [TRX Date] ,
RangeSum( [Debit- Credit], peek( 'DebitCreditSum' ) ) as DebitCreditSum //accumulation field
Order By [TRX Date];
Drop Table TableSource;
Example implementation in the attached file QVF.
Thanks, it works for that limited table (only TRX Date and Debit-Credit columns). However, since the real data has more columns I tried adding complexity by simulating a 'Nominal Code' column scattered randomly in the mix. This seems to throw off the values (e.g. it says 8 on the 4th which I was expecting to be 4, 13 on the 5th which should be 14 etc.)
Attaching the qvf file which includes the new set analysis in the table and the adjusted load script
I tried to use this code to test the concept on a more complex source table which is more akin to the live data.
I have the test data in an excel sheet but cannot seem to get the modified load script to work. Can you see what I've done wrong?
LOAD [Journal Entry],
Date(Date#([TRX Date], 'DD/MM/YYYY')) as [TRX Date],
[Back Out JE],
RangeSum( [OriginatingDebit]-[OriginatingCredit], peek( 'DebitCreditSum' ) ) as DebitCreditSum
Order By [LineRef],[Nominal Code],[TRX Date];
Drop Table Sheet1;
FROM [lib://Qlik/7100 Early test data for Qlik accumulation.xlsx]
(ooxml, embedded labels, table is Sheet1);