6 Replies Latest reply on Oct 21, 2014 5:08 AM by KK Molugu

    Creating a view showing most recent status

    Kirstin Lyon

       

      I've connected Tableau to a large customer database where customers change from one status to another at a particular stage. The only way to find out when the customer changes state, is to look at the marker. See example data below

       

       

      Example data:

       

      CUST ID timestamp Marker

       

      CustID1 2014-10-09-12.04.32.876329 N

       

      CustID1 2014-06-12-09.30.50.671042 Y

       

       

      I want to find out all customers who are still marked as Y as of today. I'm assuming I have to create a view that only shows the most recent status per customer, and then filter on marker, but I don't know how to create a view to show the recent status.

       

      I still need to have the complete data in my table for other calculations, so I can't filter the data before it comes in to Tableau.

       

        • 2. Re: Creating a view showing most recent status
          KK Molugu

          Kirstin:

           

          I think there is an easy way to solve this and hope this helps. I am using superstore data as the sample;


          STEP 1


          - 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;

          step1.PNG

           

          STEP 2

          - 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;

          step2.PNG

           

          STEP 3

          - 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)

          step3.PNG

           

          Hope this helps;

           

          ..kk

          1 of 1 people found this helpful
          • 3. Re: Creating a view showing most recent status
            Kirstin Lyon

            Hi KK,

             

            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.

             

            Thanks

            • 4. Re: Creating a view showing most recent status
              KK Molugu

              Kristen.

               

              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

               

              ..kk

              • 5. Re: Creating a view showing most recent status
                Kirstin Lyon

                Hi KK,

                 

                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.

                 

                "