topic Re: How to include a starting balance in a data set? in App Development

How to include a starting balance in a data set?

Wed, 31 Aug 2016 21:00:11 GMT

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 — Thu, 01 Sep 2016 05:57:57 GMT

Could you post a sample?

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

Thu, 01 Sep 2016 07:34:48 GMT

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?

Thu, 01 Sep 2016 14:01:50 GMT

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!

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

Fri, 02 Sep 2016 08:22:43 GMT

Hi Kenneth,

I am attaching the data and application again , I have created a new filed "Account Name Balance Year", where you will find four values,

A,B,C, D, when you will select C, you will find 100. Hope this is the actual requirement but if not please attach few rows with real values and structure so I can make a better solution for you and please mention ,what you want , like if you select this on dashboard this value you want to see