topic Re: Creating Trial Balance by Account and Period in QlikView

Creating Trial Balance by Account and Period

Fri, 07 Mar 2014 00:09:56 GMT

I'm trying to create a trial balance (from Microsoft Dynamics GP), which shows the opening balance, debits, credits, net activity, and ending balance by account by period.

Ultimately, I need the structure to look like this:

Year	Account	Period ID	Debits	Credits	Net Activity	Period Balance
2014	####	0-BEG	5,900,000.00	6,700,000.00	-800,000.00	-800,000.00
2014	####	1-JAN	8,500,000.00	9,800,000.00	-1,300,000.00	-2,100,000.00
2014	####	2-FEB	0.00	1,200,000.00	1,200,000.00	-900,000.00
2014	####	3-MAR	0.00	1,000,000.00	1,000,000.00	100,000.00
2014	####	4-APR	-100,000.00	600,000.00	500,000.00	600,000.00

I do know: how to pull in the account details, debits, credits, and calculate the net activity.

I do not know: 1) if I should calculate the net activity and period balance in the script or if I should create an expression in an object, 2) how to calculate a cumulative period balance by account (beginning with period 0 -the beginning balance- and adding the net activity by period, and then displaying that total per period).

Here's the script I've been playing with so far, but the last line of the LOAD statement is giving me issues:

GP_Activity:

LOAD

[Open Year] as [Year],

[Period ID] as [PeriodID],

[Account Index] as [Index],

[Account Number] as [GP Acct],

Left([Account Number],4) as [GP CC],

Right([Account Number],4) as [GP Class],

[Account Description] as [Description],

[Debit Amount] as [Debits],

[Credit Amount] as [Credits],

[Debit Amount]-[Credit Amount] as [Net Activity],

IF([Period ID]=0,(Sum([Debit Amount])-Sum([Credit Amount])),PEEK([Period Balance],-1)+(Sum([Debit Amount])-Sum([Credit Amount]))) as [Period Balance];

SELECT

[Open Year],

[Period ID],

[Account Index],

[Account Number],

Left([Account Number],4) as [GP CC],

Right([Account Number],4) as [GP Class],

[Account Description],

[Debit Amount],

[Credit Amount],

FROM dbo."AccountTransactions"

ORDER BY [Account Number],[Period ID];

Thank you in advance for your time

Re: Creating Trial Balance by Account and Period

Nicole-Smith — Fri, 07 Mar 2014 15:16:52 GMT

Nicole Adamczyk wrote:

I do not know: 1) if I should calculate the net activity and period balance in the script or if I should create an expression in an object

I would calculate it on the chart if possible. That way, any selections you make in QlikView will affect the calculation (if you calculate it in the script, you're stuck with static numbers).

Nicole Adamczyk wrote:

I do not know: 2) how to calculate a cumulative period balance by account (beginning with period 0 -the beginning balance- and adding the net activity by period, and then displaying that total per period).

Have a look a these two posts on how to do accumulative sums: http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/12/accumulative-sums, Calculating rolling n-period totals, averages or other aggregations

Re: Creating Trial Balance by Account and Period

Gysbert_Wassenaar — Fri, 07 Mar 2014 15:28:14 GMT

You're using an aggregation function in your load statement without a group by clause. That's causing the error. Try leaving the sum out:

IF([Period ID]=0,(([Debit Amount])-([Credit Amount])),PEEK([Period Balance],-1)+(([Debit Amount])-([Credit Amount]))) as [Period Balance];

You may have to change the order by clause in the select statement to avoid getting the period balance of a previous account instead of the previous period.

Re: Creating Trial Balance by Account and Period

SunilChauhan — Fri, 07 Mar 2014 15:29:32 GMT

I think its need group by

because you are using sum function in script and any aggregate function can not be used without Group by.

hope this helps

Re: Creating Trial Balance by Account and Period

Mon, 04 Aug 2014 13:50:38 GMT

Nicole,

Can you tell me how you connect to Microsofts Dynamic GP via qlikview. Do you connect directly? I have a new client with MS Dynamics and hoping it is an easy connection rather than an export process.

Re: Creating Trial Balance by Account and Period

Mon, 04 Aug 2014 17:12:59 GMT

Nicole, You should have the use Group By clause if you are using any Aggregation function.

Re: Creating Trial Balance by Account and Period

Mon, 04 Aug 2014 17:25:54 GMT

Hi Debbie - I am connecting directly to our Dynamics GP server. I have been able to pull historical and open transactions including the trial balance and accounts payable detail, all linked together.

Re: Creating Trial Balance by Account and Period

Mon, 04 Aug 2014 17:26:04 GMT

Thanks, I figured it out!