5 Replies Latest reply on Jul 30, 2018 2:03 PM by Joe Oppelt

    How to create daily Snapshot in Tableau?

    Sneha Wankhede

      Hi,

      I have a requirement wherein i need to calculate status of daily Total Open orders Vs Added Open Orders that day, somehow i am able to achieve how many are getting added daily, but somehow total open orders, i am unable to save the previous day snapshot, because if it is closed today, the previous day values shows a different number

       

      Currently data source is smart sheet in the below scenario.

       

      For Eg:

      Below is my test data, and based on status field and date resolved the values keep changing, but how will i restore as a snapshot for previous day in Total Open(highlighted in red)

           

      Date CreatedPart NumberStatusDate            OpenTotal Open
      07/30/18 10:34 AM25161Open30-Jul421
      07/30/18 9:33 AM25269Open27-Jul622
      07/30/18 9:30 AM25285Open
      07/30/18 8:56 AM25286Open
      07/27/18 1:37 PM25353Open
      07/27/18 1:37 PM25393Open
      07/27/18 1:37 PM25683Open
      07/27/18 1:37 PM25788Open
      07/27/18 10:46 AM25852Open
      07/27/18 7:06 AM25867Open
      07/26/18 11:18 AM25909Open
      07/25/18 9:35 AM25940Open
      07/25/18 9:35 AM25943Open
      07/25/18 9:35 AM26103Open
      07/25/18 9:35 AM26131Open
      07/24/18 11:52 AM26258Open
      07/23/18 1:02 PM26292Open
      07/23/18 9:58 AM26311Open
      07/19/18 6:10 PM26319Open
      07/09/18 9:30 AM26320Open
      07/06/18 7:16 PM26360Open

       

      Thanks

        • 1. Re: How to create daily Snapshot in Tableau?
          Joe Oppelt

          Not enough info in your problem statement to give you an answer.

           

          Can you create a sample data file in excel and build a sample workbook on that?

          • 2. Re: How to create daily Snapshot in Tableau?
            Sneha Wankhede

            Tab 1 : shows how many open status each day

            Tab 2 : shows cumulative open each day

             

            For Eg: Date     Total Open

                           7/30      21

                           7/31      20 (If 1 is closed it should show 20), but the value for 7/30 should still show as 21, because it was opened on 7/30

            • 3. Re: How to create daily Snapshot in Tableau?
              Joe Oppelt

              In the attached I made a table calc that gives you the running sum day by day.  I placed it on the "open each day" sheet.

               

              Table calcs can give us looks at past data this way.

               

              On your second tab I changed things.  I added a parameter that lets the user select what day to look at.  See calc [running sum up to selected day].

               

              Notice that I had to include the Date Created dimension on the sheet.  Tableau needs to know what days to add, and what each day's value is.  And therefore you get multiple copies of the same total, one for each day on the sheet.


              Go to version (2).  Here I added another calc called INDEX, and told tableau just to display the first of the multiple values.  This gives you the viz you need.  Your user can select whatever day to examine this way.

               

              Going back to the very first sheet, I could have instead made a filter like this:

               

              If ATTR(Date Created) = [Display Date] then 1 else 0 END

               

              And then if I put that on the filter shelf and selected for value = 1, it would only show the one date, with the proper running_sum value for that date.

               

              So there are lots of ways to get what you need.  Please ping back if you have questions about what I did in there.

              • 4. Re: How to create daily Snapshot in Tableau?
                Sneha Wankhede

                When i am doing a running_sum it works fine , but as soon as the status changes to close, the data is impacting again?

                • 5. Re: How to create daily Snapshot in Tableau?
                  Joe Oppelt

                  You will have to add a column to your data (or a separate row!) that lets you see when closure happened.

                   

                  Looking back, I see you were asking about a snapshot, and now I see why.  But tableau doesn't write anything out.  It only tells you about what data has come in.  So Tableau won't save for you what the data looked like yesterday or last week.


                  Therefore you will need to have something in the data to tell you when closure occurred.  With that, you won't be filtering on Status="Open", but rather you will filter for part numbers where the closure happened after the selected [Display Date].