Set Analysis - Max Date per customer

[darren_dixon]

Hi,

How do I turn the following data into the straight table below.

This set analysis returns a null:

sum({<[Transaction Date]={'$(=max([Transaction Date]))'}, [Account]={'Main'}>} [Transaction Value])

Data

Customer	Account	Transaction Date	Transaction Value	Transaction Code
Customer 1	Main	10/01/2018	£10.00	Cash
Customer 1	Sub	09/01/2018	£20.00	Card
Customer 1	Main	08/01/2018	£30.00	Card
Customer 2	Main	02/01/2018	£40.00	Cash
Customer 2	Main	01/01/2018	£50.00	Card
Customer 3	Main	16/01/2018	£60.00	Card
Customer 3	Sub	10/01/2018	£70.00	Cash
Customer 3	Main	12/01/2018	£80.00	Card
Customer 3	Main	08/01/2018	£90.00	Card

Straight table

Customer	Account	Transaction Date	Transaction Value	Transaction Code
Customer 1	Main	10/01/2018	£10.00	Cash
Customer 2	Main	02/01/2018	£40.00	Cash
Customer 3	Main	16/01/2018	£60.00	Card

Please can you help?

Thanks,

Darren

[sunny_talwar]

Try this

FirstSortedValue({<[Account]={'Main'}>} Aggr(Sum({< [Account]={'Main'}>}[Transaction Value]), Customer, [Transaction Date]), -Aggr(Only({< [Account]={'Main'}>} [Transaction Date]), Customer, [Transaction Date]))

[sunny_talwar]

Here is a sample attached

[darren_dixon]

Hi Sunny,

How would I add the Transaction Code so the table displays like this:

Customer	Account	Transaction Date	FirstSortedValue	Transaction Code
		16/01/2018	60
Customer 1	Main	10/01/2018	10	Cash
Customer 2	Main	02/01/2018	40	Cash
Customer 3	Main	16/01/2018	60	Card

When I add the dimension I get extra lines.

Customer	Account	Transaction Code	Transaction Date	FirstSortedValue
			16/01/2018	60
Customer 1	Main	Cash	10/01/2018	10
Customer 1	Main	Card	08/01/2018	30
Customer 2	Main	Cash	02/01/2018	40
Customer 2	Main	Card	01/01/2018	50
Customer 3	Main	Card	16/01/2018	60

[sunny_talwar]

add it as an expression