6 Replies Latest reply: Jun 22, 2016 8:56 AM by Matthew Berry RSS

    Accumulate balance shown by date

    Matthew Berry

      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.

       

      Example:

      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.

        • Re: Accumulate balance shown by date
          Sunny Talwar

          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
            Andrew Walker

            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:

             

            Balances:

            LOAD * INLINE [

                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

            LOAD

            Min(Date) as StartDate

            Resident Balances;

             

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

            DROP Table TempMinDate;

             

            Periods:

            Load

            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.

            Balance

            Resident Balances;

             

            Drop Table Balances;

             

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

            Load

            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