Return a value based on field's value from a different alternate state

[amirkachlon]

Hi All,

appreciate your assistance

Current State:

I have a table of transactions per supplier per quarter, as described below:

Quarter	Transaction Date	Supplier Code	Item Number	Cost ($)	Last Transaction of the Quarter
Q1'18	24/07/18	Supplier 1	AAAA	$10,235.17	0
Q1'18	26/07/18	Supplier 2	AAAA	$8,920.00	1
Q2'18	24/10/18	Supplier 2	AAAA	$8,920.00	0
Q2'18	25/10/18	Supplier 1	AAAA	$9,911.81	1

The green lines are the last transactions per quarter (with no reference to supplier).

I use two alternate states to show the last transactions per quarter, so when the user choose Q1'18 in [alternate state 1]

 and Q2'18 in [alternate state 2] he will get the two green lines as a result.

Challenge:

Knowing that the last transaction for Q2'18 in [alternate state 2]  is from Supplier 1, I wish to find the last transaction for Supplier 1 for Q1'18 in [alternate state 1].

meaning, in the example the result will be the transaction marked in blue.

Quarter	Transaction Date	Supplier Code	Item Number	Cost ($)	Last Transaction of the Quarter
Q1'18	24/07/18	Supplier 1	AAAA	$10,235.17	0
Q1'18	26/07/18	Supplier 2	AAAA	$8,920.00	1
Q2'18	24/10/18	Supplier 2	AAAA	$8,920.00	0
Q2'18	25/10/18	Supplier 1	AAAA	$9,911.81	1

End Result:

The marked green calculation are available in my solution what I'm missing is the calculation described above marked in red

When I choose a specific item it works, but when no item is selected it seems that its not calculated properly

Item Number	Alternate State 1 Transaction Cost	Alternate State 2 Transaction Cost	Alternate State 2 Supplier Code in Alternate State 1 Quarter
AAAA	$8,920.00	$9,911.81	$10,235.17
BBBB	$7,920.00	$9,911.81	$8,235.17

Hope to find a helper : )

Thanks

Amir

[sunny_talwar · Accepted solution]

Try this expression

=Sum({<Quarter = Alt1:: Quarter, Meh = {0}>} Aggr(
  If(Supplier = Only(TOTAL <Product> Aggr(If(Date = Max(TOTAL <Product> {<Quarter = Alt2::Quarter>} Date), Supplier), Product, Date))
  , Sum(Amount))
, Product, Supplier, Date))

[amirkachlon]

Great, what i was missing is the use of the total by product. Great solution thanks