10 Replies Latest reply on Sep 16, 2016 11:16 AM by Patricia Everett

Multiple Dates and ratings

I am working on pulling several sources together and have Tableau 9.1 also have Tableau 10.0 but our server is 9 so I can't use 10 to develop my data for the server. I have a listing of suppliers with the statement date, meaning their financial statements (could be at least 3 or more) and then what the company calls their rating. The rating can be from Excellent to Concern and can change based on their financial health. I am trying to just pull the latest statement date with the rating for that date. I have figured out how to do this using a fixed calculation in 10 but can't seem to work this in 9.1. Any help would be greatly appreciated.

• 1. Re: Multiple Dates and ratings

Hi Patricia, just to clarify, are you trying to get the latest statement date and rating for each company? Or across all companies?

• 2. Re: Multiple Dates and ratings

Hi David,

Thanks for the question. The latest statement date and rating for each company. So there are probably 1300 companies on the list with multiple dates and I need to just show what the latest rating is and when the statements were reviewed. Some companies may publish quarterly financials and other may be only twice a year.

I greatly appreciate all of your help.

Patricia

• 3. Re: Multiple Dates and ratings

Ah, I understand. You can do this by dropping all of the companies, statement dates, and ratings into a view and then using a table calculation filter:

LAST() = 0

This works by finding the last value in each partition, and we're going to set the partition to Company and sort on Statement Date.

Here's an example I did with the Superstore data. Imagine that Sub-Category is Company, the Order Date is the Statement Date, and that the Order ID is the Rating.

For the table calculation, make sure that it's computing with Company in Partitioning and everything else in Addressing. Also make sure that it sorts on Statement Date. Then, make sure the filter keeps TRUE.

• 4. Re: Multiple Dates and ratings

Oh, and just in case you want to avoid using table calculations, there's a method using level-of-detail calculations, as well. Let me know if you're interested in that.

• 5. Re: Multiple Dates and ratings

Hi David,

Thanks for the information so far. It's been helpful. Yes if there is a way to do this with a level-of-detail calculation, that would be great also as long as it works in version 9.1

Thanks!

Patricia

• 6. Re: Multiple Dates and ratings

Yes, LOD calcs should work in version 9.1. You essentially need to create a LOD calc that finds the maximum date for each Company. Then, you compare that to the other records so that it filters in only the one that has the max date. You should be able to do this all in one calculated field. Something like this:

[Statement Date] = { FIXED [Company] : MAX([Statement Date]) }

Drop that into the filters and include TRUE.

• 7. Re: Multiple Dates and ratings

Hi David,

Thanks so much for the information. I tried this and it's coming back with

an error:

level of detail expressions are not supported by excel data source.

Any ideas?

Patricia

On Fri, Sep 16, 2016 at 9:29 AM, David Li <tableaucommunity@tableau.com>

• 8. Re: Multiple Dates and ratings

David,

I actually redid the calc and it's working now. Not sure why it wasn't

before but it's working!!! Thank you so much!

Patricia