Cummulative amount by date selection

BrunPierre — Thu, 11 Mar 2021 04:33:50 GMT

Hi guys!

I'd like to perform a cumulative sum by date preferably in the scripts.

In the table below column(1) has the raw values, whilst column(2) displays the cumulative figure.

Now upon selecting a date i.e. 12/10/2020 this expression "(RangeSum(Above(SUM({<%Date={"<=$(=MAX(%Date))"} >}m_InterestAccruals),0,RowNo())))" , sums up from 22/09/2020 to give a correct output of 258,655.08

My challenge is getting only this total figure of 258,655.08 just a single row each and each time a date is selected.

Sample data attached, thanks.

Re: Cummulative amount by date selection

MayilVahanan — Thu, 11 Mar 2021 06:15:26 GMT

HI @BrunPierre

Try like below

Temp:
LOAD %ID,
%Date,
Amount
FROM
[dummy.xls]
(biff, embedded labels, table is Sheet1$)
Where %ID <> 'Total';

LOad *, If(%ID = Peek('%ID'), Amount+Peek('Cummulative'), Amount) as Cummulative Resident Temp Order by %ID, %Date;

DROP Table Temp;

topic Re: Cummulative amount by date selection in QlikView

Cummulative amount by date selection

Re: Cummulative amount by date selection