Changing NL formula to GL

Put your best Excel related tips and tricks here.
Forum rules
This is the place to put all of your Excel Tips & Tricks.
GWRUK
Posts: 6
Joined: Thu May 28, 2015 2:21 am

Changing NL formula to GL

Postby GWRUK » Mon Aug 03, 2015 6:32 am

Hi,

Would you be able to advice on the following;
We have a Balance sheet (same for P&L) with the following formulas which we would like to change to GL instead, mainly for load up speed issues;

=NL("Sum","vwTCL_GL_Summary_Balances","Period Balance","Account Number",$E86,"Period ID",G$1,"Year",Options!$F$5,"company=",G$2)

What would you suggest to do to change to GL instead. We do not require the transaction load up, just balances as of the period in question.

Thanks.

zuzana

User avatar
The Kloser
Posts: 159
Joined: Mon Jan 07, 2008 5:08 pm

Re: Changing NL formula to GL

Postby The Kloser » Thu Aug 06, 2015 9:07 am

Hi zuzana,

It looks like you are using a custom GP view for this function. Depending on your system and the customizations you have added, the GP GL function might not work as expected. For help on using the GP GL, I would recommend checking out the KB article on it: http://kb.jetreports.com/article/AA-00531 Since you are using Period ID and year inputs, you will want to use the PeriodID/Year syntax in the GP GL. Use the 4 digit year followed by the period ID. And example would be: "2015/05"

If you are trying to just get the period balance for 1 period, use this for for the Start Period and the End Period in the function.

Here is an example of what your function might look like (using the cell references from your function provided):

=GL("Cell","Balance",$E86,Options!$F$5&"\"&G$1,Options!$F$5&"\"&G$1,,,,,,,,,,,,,,,,,G$2)

You can see I just used Excel syntax to combine the year and period ID for the start and end period.

I hope this helps!

GWRUK
Posts: 6
Joined: Thu May 28, 2015 2:21 am

Re: Changing NL formula to GL

Postby GWRUK » Fri Aug 07, 2015 4:07 am

Hi,

Thank you for your reply.
Would this help if I wanted to pull through year to date figures. For example I would like to see financial results for start of the period (January) to end period (July).

Also would this work next reporting month ie January to August.

Sorry if this is trivial from the advice you have given, I am new to Jet and it helps me with learning. (especially the fine limitations of the formulas)

Many thanks.

Zuzana

User avatar
The Kloser
Posts: 159
Joined: Mon Jan 07, 2008 5:08 pm

Re: Changing NL formula to GL

Postby The Kloser » Fri Aug 07, 2015 7:16 am

I apologize. I know what it is like when you are first starting out. Jet is quite powerful and learning it can be a bit overwhelming.

Are your periods linked to the months? I am asking if January is period 1 and July is period 7. If you are trying to find the net change from January to July then use "2015/01" for the Start Period and "2015/07" for the End Period. To find January through August, use "2015/01" and "2015/08". Does this make sense?

Please note that in my original response I put the slashes backwards. This is the proper syntax. I copied this from the Jet Help: "YYYY/PPP where YYYY is the 4 digit fiscal year and PPP is the 1 to 3 digit period number."

I hope this helps!

GWRUK
Posts: 6
Joined: Thu May 28, 2015 2:21 am

Re: Changing NL formula to GL

Postby GWRUK » Fri Aug 07, 2015 8:09 am

Very grateful for your help here. :)

Would I then need to change my formulas manually every month? By changing manually I mean opening my reports in design mode and altering every single revenue/cost line and adding `2015/08`. Or a refresh Option would be enough, where I would just change the end month.

Months are indeed covering the periods (January is for 01).
Thank you

Zuzana

User avatar
The Kloser
Posts: 159
Joined: Mon Jan 07, 2008 5:08 pm

Re: Changing NL formula to GL

Postby The Kloser » Fri Aug 07, 2015 8:30 am

The best option would be to have the user enter in the end period on the options page. Then use cell references in your functions. This will allow both Designers and Viewers to run the report and change filters. I would recommend using the syntax I gave in my first response (but with the slash going in the correct direction).

For instance....

Cell B2=2015 -> for the year
Cell B3=1 -> for the start period
Cell B4=6 -> for the end period

In your function, you can combine these using Excel. Let's start with the Start Period. We need it to be "2015/1" so we will use this inside the GL function:

$B$2&"/"&$B$3

For the End Period, we need it to read "2015/7" so we will use this:

$B$2&"/"&$B$4

Does this help?

GWRUK
Posts: 6
Joined: Thu May 28, 2015 2:21 am

Re: Changing NL formula to GL

Postby GWRUK » Fri Aug 07, 2015 8:37 am

Thank you, it really helps.


Return to “Excel Tips & Tricks”

Who is online

Users browsing this forum: No registered users and 2 guests