1 of 1 people found this helpful
I think there is an easy way to solve this and hope this helps. I am using superstore data as the sample;
- Customer ID to Row Shelf
- Order Date to Row Shelf - MDY(Order Date) (select custom on the order date and select M/D/Y datepart)
- Category to Row Shelf
This should make your view looks likes this;
- In this example, I used Paper as my status (for your Y and N)
- Dragged category to filter shelf and only select Paper (in your case Y)
View would look like this. Now you have more papers for the same customer at diff dates;
- Right click on MDY(Order Date) pill and select Measure and select maximum
- View will look like below with one record per customer with max date with status = Y (in this case paper)
Hope this helps;
Thanks for the suggestions! I'm completely new to tableau (and SQL) so I'm not sure how to do the first option, and the second option gives me the latest time a customer was marked Y.
All customers start with Y, and then switch over to N once a particular task is completed, and I need to be able to generate an overview of the current status (Y or N) for each customer. They can go back and forward between Y and N a few times, so that's why the timestamp is so important.
No problem and we sre here to help each other. Iso you want :
1. Allcustomers with most recent Y status regardless.or
2. Yoy want most recent status regardless of the status
If you can send me some data that would be better even 100 recs from your data set. in addition to that also sene me how ur output should look like a sample so i am all clear on what ur looking for
I was looking for the customers with most recent status, regardless if it was a Y or N. I actually got this response from another forum (thanks to Justin Larson!) which seems to work - thanks again for the support :-)
Without using the filter, I believe the most straight forward approach would be to create a window (table) calculation. Depending on your data source, a custom SQL may actually be easier, but it depends on your level of comfort with messing with data connections, and is only available with certain kinds of data sources.
For the window calculation, create a calculation that looks like:
if first()=0 then attr([Marker]) end
Drop CustomerId onto rows, exact date onto level of detail, and your newly minted calculation onto label.
At first, you will just see a bunch of values from your marker field all listed out next to each customerid. Click on the new calcuation pill, and select Edit Table Calculation, then select Advanced from the Compute Using dropdown. Make sure CustomerId is the only item in partitioning, and make sure the sort option at the bottom shows datestamp field used for sorting, and choose max, descending for the other two.
When you hit ok,ok, all of the values that are not the latest one should go null at this point. The trick for this calculation to work is that both customerid, and datestamp (exact date) must be in the view to work. Datestamp can just be in the level of detail, but it has to be there.
If you are not familiar with table calculations, I suggest you give yourself plenty of time to read up on them. They are where Tableau begins to get pretty powerful, but can be pretty confusing. If you go to the forum and just search "latest" you will see a whole bunch of threads trying to accomplish different variants of the same problem. (get latest value for XYZ scenario) and people love posting examples, so you can see different nuances of approaches to similar problems inside of actual packaged workbooks.
The calculation, in short, takes your data and partitions each distinct customerid into it's own little box. Then orders the data by datestamp descending (newest stuff at top) and reports back the first instance of the "marker" value.
Just remember, Customerid must be the ONLY field in that partitioning window, and if you really want latest marker value, it must be sorted descending on max(datestamp) in the calcuation.