Return value of a field based on value in another expression

[#1]

Hi

I need to return the transaction type of the latest transaction as per below. That is, for a given stock item/customer/project etc. as defined in the cyclical dimension group, I can find the last transaction date using max(TransactionDate). What I need to do is to return what that transaction was e.g. Customer Order, Customer Invoice, Credit note etc.

Any ideas?

[ramoncova06]

you can use firstsorted value

firstsortedvalue(LastActivityType,-aggr(max(TransactionDate),ItemID))

[#3]

Thanks Ramon.

In the chart, the ItemID field is actually part of a cyclical group. Is there anyway that you know that I could reference that instead of naming every field in the group in the expression statement?

[ramoncova06 · Accepted solution]

this is what I have used $(='['&GetCurrentField([Group])&']') before so it should something like

firstsortedvalue(LastActivityType,-aggr(max(TransactionDate), $(='['&GetCurrentField([Group])&']') ))