Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating Trial Balance by Account and Period

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:

YearAccountPeriod IDDebitsCreditsNet ActivityPeriod Balance

2014

####0-BEG5,900,000.006,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-FEB0.001,200,000.001,200,000.00-900,000.00
2014####3-MAR0.001,000,000.001,000,000.00100,000.00
2014####4-APR-100,000.00600,000.00500,000.00600,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

7 Replies
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.


talk is cheap, supply exceeds demand
SunilChauhan
Champion
Champion

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

Sunil Chauhan
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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. 

Not applicable
Author

Thanks, I figured it out!