Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Leverage your QlikView investment to modernize BI – see how! Join Group
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Accumulate balance shown by date

I would like to display the balance from the first dump to a specific date. Not every date has transactions. I want to look up the closest day before the date I enter in the selection in a report in QV and display the balance.


Date             Balance

5/5               9,000

5/9               7,000

5/18             4,000

5/23             5,000

I want to enter date 5/12/16 and get the response $7,000, which was the balance on 5/9/16 when the item was last touched. Note - there were no changes for this item between 5/9/16-5/18/16.

6 Replies

Re: Accumulate balance shown by date

May be like this:

FirstSortedValue({<Date = {"$(='<=' & vVariable)"}>}Balance, -Date)

Here vVariable will be driven by an input box or calendar box object


Re: Accumulate balance shown by date

Sample to take a look at



Re: Accumulate balance shown by date

Hi Matthew,

Try this code, it will give what might look like an odd data model and you can tidy it up if you want but it's good to leave it as it is so you can get to understand how the IntervalMatch function works.

Add a listbox for Date and a text box with the formula =Only(Balance) and play around with it:



    Date, Balance

    5/5/2016, 9000

    5/9/2016, 7000

    5/18/2016, 4000

    5/23/2016, 5000


TempMinDate: //Get the start date for making a calendar


Min(Date) as StartDate

Resident Balances;

Let vStartDate = PEEK('StartDate',0,'TempMinDate');// start date held in this variable

DROP Table TempMinDate;



Date as From,

Date(RangeMin(Today(),Peek('Date',RecNo(),'Balances') -1)) as To,

//The above line peeks ahead one record in the Balances table to see the To Date and subtracts 1.

//If Peek does not return a value (when reading last record and there is no next record to peek) Today() is returned.


Resident Balances;

Drop Table Balances;

Calendar: // Make a simple calandar of dates from start to today


Date(TempDate) as Date;

LOAD IterNo() + $(vStartDate) -1 as TempDate

AutoGenerate 1 While IterNo() + $(vStartDate) -1 <= Today();

IntervalMatch(Date) LOAD From, To Resident Periods; // This matches each date in the calendar to a time period

Not applicable

Re: Accumulate balance shown by date


Thank you for your response. Unfortunately I cant get that to work. Not sure what I did wrong



Re: Accumulate balance shown by date

Would you be able to share where you trying this and what is the expected output?

Not applicable

Re: Accumulate balance shown by date


View attached.

My question is: I would like to be able to use a Calendar (based from the Date dimension preferably) and select on any day and it show the balance.

For example:

- I select 6/3/16 on the calendar object and the balance shown is 73,017.956.

- I select 5/29/16 on the calendar object and the balance shown is 76,273.555 (from last balance at 5/27)