Expression to get sales person name from most recent invoice

[Anonymous]

In my business, the name of the sales person is stored on each customer invoice. Over time the sales people move around to different jobs, so the name of the sales person on each customer invoice will change over time. However the sales person whose name is on the most recent invoice is the one who is currently responsible for that customer.

I am creating a table report where each row in the report represents one customer, having the following columns..

Column #1 => Customer ID

Column #2 => Customer Name

Column #3 => Customer Address

Column #4 => =Sum([Net Invoice Price])

Column #5 => =Text({<[Billing Date]=($(=Max(TOTAL [Billing Date]))>} [Sales Person])

My (only) problem is that the Expression for column #5 does not work. Could somebody kindly help to identify what I am doing wrong?

[sergio0592]

Try with :

In  a text box

=FirstSortedValue([Sales person],- Billing Date)

As expression or calculated dimension in a straight or pivot chart

=FirstSortedValue(TOTAL [Sales person],- BillingDate)

[Anonymous]

‌Perfect. Many thanks. In fact I found that FirstSortedValue(DISTINCT ... ) was the right solution.

[Anonymous]

firstsortedvalue (distinct ...) --> you have to decide if it is sufficient for your needs taking the first firstsortedvalue in load order...

alternatively you could use aggr()  ...

[Anonymous]

For the avoidance of doubt I found the following solution to work perfectly for me..

=FirstSortedValue(DISTINCT [Sales person], -[Billing Date])

The reason for the DISTINCT seems to be that we have some billing dates where the [Sales person] has a NUL record value, and FirstSortedValue seems to always sort NULL values first (bug maybe); however the DISTINCT makes sure that only non null values are returned.

[Anonymous]

You're wrong I think...

  Firstsortedvalue will return null() if there's no or more than 1 distinct "firstsortedvalue"!

Null is not a value!

[Anonymous]

It returns values that display as '-' ..  That looks like a null to me.

[Anonymous]

It's hard to explain what I mean, if distinct works for you, than everything is fine...

[Anonymous]

Yup. As I said, it works fine for me.

[Anonymous]

then close the thread