Intercompany Eliminations (removing intercompany transactions)

[JY1]

Hi,

I am new to Qlik Sense, currently working on a project to remove intercompany transactions (intercompany eliminations).

For example, Subsidiary Company A lends or invoices to Subsidiary Company B, this transaction is obviously relevant to both companies' financials. However, as both companies are under Parent Company ABC, when looking at the financials for the Parent Company this transaction should be removed as it artificially inflates the revenue and debt, hence Intercompany Eliminations.

I am trying to create an application where all the financials are present including the intercompany transactions between the two subsidiaries, but if I were to select the Parent Company the Intercompany the transaction would be removed. This would normally be easy using a marker and set analysis, however the data inputted is not always correct and there are mixed data. There has been a move to simplify the intercompany transactions Therefore, I need a check in place to separate these results. At the end of this, there would be 3 tables; All data, exc. intercompany transactions and check.

Using the table below as an example, there are two ways to identify an intercompany transaction, via the Code and Department. Code is the type of transaction, Dept is the Company Number, the Date is the Transaction date.

Code 4000 and Code 7500 are indicators of Intercompany Transaction, as one highlights giving and the other receiving money, though this should match with the date and amount. The other indication is the Dept 099 (which has been used in some of the data to highlight an intercompany transaction)

In the table below Rows 1 and 7, because they are completed on the same day, the amount matches and Codes are 4000 and 7500, we can say they are an intercompany transaction that should be removed, the same as rows 9 and 10.

 Rows 2 and 6 have Dept 99, but the amounts do not match, even though the Codes and dates are high indications that it should be, therefore it needs to be marked as a check.

So if we bring this back to the goal, All data is all the rows, exc. Intercompany transactions is rows 2-6 and row 8, and check is rows 2 and 6.

Please help.

Thanks in advance,

JY

Rows	Date	Code	Dept	Amount
1	23/07/2021	4000	106	5000
2	19/12/2021	4000	99	550
3	06/10/2021	5100	105	-10000
4	05/12/2021	2100	126	7500
5	05/12/2021	6000	106	8000
6	19/12/2021	7500	99	-450
7	23/07/2021	7500	110	-5000
8	06/10/2021	9000	115	10000
9	24/07/2021	4000	106	5000
10	24/07/2021	7500	110	-5000

[chrismarlow · Accepted solution]

Hi,

You could start with something like the script below that tries to identify the offsetting IC transactions. It could be then as simple as joining back between Rows and 4000Rows and 7500Rows (two separate loads, then another to tidy up) to identify 'normal' ICs where both 4000Rows and 7500Rows are populated and 'check' ICs where only one is.

I've not done that as i think you are probably going to need some other business rules here, so this could create duplicates (or messier, maybe you could have 4 transactions for the same date, 2 4000's, 2 7500's and one of the 7500's could pair off to both 4000's?) and how would you want to handle that?

Does this help?

Cheers,
Chris.

data:
load * inline [
Rows,Date,Code,Dept,Amount
1,23/07/2021,4000,106,5000
2,19/12/2021,4000,99,550
3,06/10/2021,5100,105,-10000
4,05/12/2021,2100,126,7500
5,05/12/2021,6000,106,8000
6,19/12/2021,7500,99,-450
7,23/07/2021,7500,110,-5000
8,06/10/2021,9000,115,10000
9,24/07/2021,4000,106,5000
10,24/07/2021,7500,110,-5000
];

offsets:
Load
	Date as D,
    Rows as [4000Row],
    -Amount as A
resident data
where Code=4000;

join (offsets)
Load
	Date as D,
    Rows as [7500Row],
    Amount as A
resident data
where Code=7500;

[JY1]

Hi Chris,

Apologies for the late response, this format did work. 

Cheers,

Jarren

[chrismarlow]

Hi Jarren,

Glad it helped!

Cheers,

Chris.