4 Replies Latest reply: Sep 2, 2016 4:22 AM by Rohit Kumar RSS

    How to include a starting balance in a data set?

    Kenneth Edwards

      I am pulling balance sheet data from quickbooks in an xls detail report.  Because it's a balance sheet, each line item includes the amount of the asset or liability, but maintains a running balance from the year before.  The report contains the amount (per 'transaction') in one column and the running balance in another.

       

      I'd like to use the running balance in qlik somehow but when filtering by Account Type, the balance becomes meaningless as its a running total relative to the date.

       

      If anybody has a solution or different approach to overcome this it would be a big help.

        • Re: How to include a starting balance in a data set?
          Robert Mika

          Could you post a sample?

            • Re: How to include a starting balance in a data set?
              Rohit Kumar

              Hi,

              what I got from your blog, You need to get the latest running balance by date when you select an account type. I have created a sample application where there are two account type, A and B. when you select A it will give you the current or latest running balance. I am attaching the sample sheet too. Please let me know if my solution match your requirement.

               

              Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                • Re: How to include a starting balance in a data set?
                  Kenneth Edwards

                  Thanks Rohit,

                   

                  I modified your excel sheet to show what the data looks like.  Digging into it I've realized that the balances are kept separately for each account type.  That is, the balance for the account is the accounts balance from the year before plus all of the transactions for that account.

                   

                  Now the issue is that there are previous year balances for accounts that don't have any current year transactions.  In the attached excel sheet, Account C is the problem.  That balance is data I am interested in seeing, but the database unfortunately doesn't populate any of those total rows with the account info, so I'm unsure how to retrieve that in qlik.

                   

                  Thanks again for the help!