Obtain Last Assigned Sales to a provider

[ljtrzebinski]

Hi all,

Hope someone can help me out with my request. I am trying to create a report that will show "ProviderName", "LastAssignedSalesName", and "Revenue Generated".

Please note that there could be more than one person assigned during the year to the provider listed. I just need most recent name.

I have two tables:

Job Detail - Job Detail has the job number, provider name & Sales Name.
Sales - Sales has job number, shift worked, and revenue generated.

I though that I could accomplish this by using lastnonblank

LastSales=LASTNONBLANK(Job[SalesName],[Total Revenue]) - It works but sales name is the last with the highest revenue generated rather than last assignment.

Any other suggestions?

 

[ljtrzebinski]

Hi all,

Hope someone can help me out with my request. I am trying to create a report that will show "ProviderName", "LastAssignedSalesName", and "Revenue Generated".

Please note that there could be more than one person assigned during the year to the provider listed. I just need most recent name.

I have two tables:

Job Detail - Job Detail has the job number, provider name & Sales Name.
Sales - Sales has job number, shift worked, and revenue generated.

I though that I could accomplish this by using lastnonblank

LastSales=LASTNONBLANK(Job[SalesName],[Total Revenue]) - It works but sales name is the last with the highest revenue generated rather than last assignment.

Any other suggestions?

Any one? Is there no other way to accomplish this?

 

[akice]

You need to keep in mind the native sort order of the various data types. Numbers and Dates are as expected(largest number or latest date is last), but 'Text' data type (I assume 'SalesName' is text) is alphabetical. So LASTNONBLANK in the formula you listed will return the alphabetically last [SalesName] that has any [Total Revenue].

Also, is there a relationship between the two tables? if not, i believe [Total Revenue] will return the same, total value for all [SalesName] and hence always return the last alphabetical [SalesName].

If there is a 'Date' field in the 'Job Detail' table, that would be the ideal field to find the last values you are looking for.

 

[Matt Allington]

Any other suggestions?

yes, please post a link to a sample workbook

 

[ljtrzebinski]

You need to keep in mind the native sort order of the various data types. Numbers and Dates are as expected(largest number or latest date is last), but 'Text' data type (I assume 'SalesName' is text) is alphabetical. So LASTNONBLANK in the formula you listed will return the alphabetically last [SalesName] that has any [Total Revenue].

Also, is there a relationship between the two tables? if not, i believe [Total Revenue] will return the same, total value for all [SalesName] and hence always return the last alphabetical [SalesName].

If there is a 'Date' field in the 'Job Detail' table, that would be the ideal field to find the last values you are looking for.

Thanks for your response. There is a relationship between 'Job Detail' table and 'Sales' table, the revenue being calculated is correct. As well it appears to me that the [SalesName] it returns is the one with the highest revenue number for that specific [Provider], rather than being in alphabetic order.

There is no date in job detail table, but there is shift date in the sales table.

 

[akice]

yes, please post a link to a sample workbook

Have to agree with Matt Allington. At this point a sample workbook is easiest way to properly answer the question. (Quick rant: if more people supplied a sample, questions would be easier and faster to answer. Too bad this board doesn't allow uploads.)

And LASTNONBLANK doesn't take into account whether Revenue is the highest value or not, only if a value exists. Put another way, LASTNONBLANK is an iterator function that returns a single row, single column table that is then converted to a scalar value. It iterates over the column provided as the first parameter (in native sort order) and returns the last value from the first parameter that has a non-blank value with second parameter. That's it. It doesn't check to see which value for Revenue is largest. So your comment about returning a value based on largest revenue is a coincidence.