Creating Trial Balance by Account and Period

[#1]

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

[Nicole-Smith]

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

[Gysbert_Wassenaar]

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.

[SunilChauhan]

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

[#5]

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.

[#6]

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

[#7]

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. 

[#8]

Thanks, I figured it out!