Reverse references

[#1]

Hi, i have a table with items transfers between docks. This looks like:

ITEM	FROM	TO	TRANSFER_ID
PRODUCT 1	DOCK1	DOCK2	A001
PRODUCT 2	DOCK2	DOCK3	A002
PRODUCT 3	DOCK2	DOCK3	B003
PRODUCT 2	DOCK3	DOCK1	A003
PRODUCT 1	DOCK2	DOCK1	B004
PRODUCT 3	DOCK3	DOCK1	A004
PRODUCT 1	DOCK1	DOCK3	B005

I need make a pivot table to show the number of transfers between docks - this is easy - and change the color of cross transfers product - this is a product moved from dock A to dock B and moved from dock B to dock A. The result pivot table may look like:

	DOCK1		DOCK2		DOCK3
ITEM	DOCK2	DOCK3	DOCK1	DOCK3	DOCK1	DOCK2
PRODUCT 1	1	1	1
PRODUCT 2				1	1
PRODUCT 3				1	1

I try with make variables to check the cross transfers:

vDockFrom = Only(FROM);

vDockTo = Only(TO);

=IF (COUNT ({$< FROM={'$(#=vDockTo)'}, TO={'$(#=vDockFrom)'} >} DISTINCT TRANSFER_ID)>0, RGB(255,204,0) )

but this don't work.

Anyone can help me?

[#2]

Thanks, with this now i can extend the solution - with interval dates - and make a full solution.

[#3 · Accepted solution]

I make this solution to extend the graph with interval time.

Extend the original table with a "code" to represent the relation between docks. I.E. Dock A to Dock B and Dock B to Dock A is coded "TA_B" in a new field.

'T'& RangeMinString( right(FROM,1), right(TO,1) ) &'_'& RangeMaxString( right(FROM,1), right(TO,1) ) AS CODE,

ITEM	FROM	TO	TRANSFER_ID	CODE
PRODUCT 1	DOCK1	DOCK2	A001	T1_2
PRODUCT 2	DOCK2	DOCK3	A002	T2_3
PRODUCT 3	DOCK2	DOCK3	B003	T2_3
PRODUCT 2	DOCK3	DOCK1	A003	T1_3
PRODUCT 1	DOCK2	DOCK1	B004	T1_2
PRODUCT 3	DOCK3	DOCK1	A004	T1_3
PRODUCT 1	DOCK1	DOCK3	B005	T1_3

And the Pivot Table Graph is:

	FROM	DOCK1		DOCK2		DOCK3
	TO	DOCK2	DOCK3	DOCK1	DOCK3	DOCK1	DOCK2
ITEM	CODE	T1_2	T1_3	T1_2	T2_3	T1_3	T2_3
PRODUCT 1		1	1	1
PRODUCT 2					1	1
PRODUCT 3					1	1

Expresion:

=COUNT(DISTINCT TRANSFER_ID)

BackGround:

= IF (COUNT(DISTINCT TRANSFER_ID)<>COUNT(DISTINCT TOTAL <ITEM,CODE> TRANSFER_ID), RGB(255,205,0) )

With this solution i can show cross transfers products for a interval date of transactions.

Thanks to jpe‌ to light the path.