0

TB by Dimension - excluding combos resulting in £nil

Jason Seegers

August 13, 2023 20:46

None

Follow

Good day,

I'm a little new to Jet and trying to create a trial balance by dimension in order for Management to start analysing information properly. This is the first time we as a business has split our business into business units and also introduced cost centres.

I managed to create a trial balance by these two dimensions but the result is an epic 4000 row trial balance due to the sheer volume of £nil balances with the various combos. This file takes roughly 25 minutes to generate and so not ideal when crunch time around month end but also quite unwieldly to put into the management reporting pack.

Could someone please help explain how this can be achieved?

The formula I currently have is as follows: =NL("Sum","G/L Entry","Amount","G/L Account No.",$D11,"Posting Date",H$5,"BU Code",$E11,"Dept Code",$F11,"Amount","<>0")

For reference, the BU code is Global Dimension 2 and the Departments are Shortcut Dimension 3. Not sure if that is required for any assistance. 

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  BryanAtTigunia August 14, 2023 16:11

  What is the replicator you are using?  Ideally, you should only return relevant accounts.  And if you are trying to get combination of values, you will need to use a field cache.  

  I don't think the NL Sum is the issue and the filter for <>0 isn't doing much.

  You can get only accounts that had activity by using something like "Rows", "GL Entry","GL Account No.",Posting Date",H$5,"BU Code",$E11,"Dept Code",$F11)  That would return accounts that had a transaction in that time period but that wouldn't be able to show accounts with balances at the beginning or end.  You can get a bit creative but working with a consultant could help.

• 0
  
  Jason Seegers August 15, 2023 14:32

  Thanks. I guess I may have to turn to the consultants. Sounds like it may be the only way.