4 Replies Latest reply on Jan 9, 2018 8:04 AM by Shivi Bhatia

    Finding Last Time Stamp from Daily Data

    Shivi Bhatia
    Shivi Bhatia Jan 6, 2018 9:36 AM

      Dear All,

       

      for one of my use case i need to find the last time when we had received volume for that day & its corresponding value, below is the just the jist of data i am dealing with. I have more than a 100k rows of data.

       

      Per the example below, we have multiple entries every hour. Assuming i have a 12 hour window from 9am to 9pm I want to know what is the volume i received at the end of the day. So for example, per the table below my calc should be in such a way that for 23rd i should have volume displayed as 6. Similarly for other days and i need to keep atleast a week in view.

       

      I have tried fixed calculation and then applied max but in that case i am getting  max only for a given day and not a week running. Another try was i calculated 2 calc fields - one which gives me max of time of computation in hour and other max of minute - but in this case also i need to manually select the date else it gives me max hour as 9pm but max minute could be anything from entire day.

       

      Please advice how to achieve.

       

        

      Time of computationTotal Cases
      23-08-2017 09:0910
      23-08-2017 09:396
      25-08-2017 04:447
      25-08-2017 04:149
      25-08-2017 09:4412
      28-08-2017 05:4414
      29-08-2017 10:3222
      29-08-2017 10:5531
      31-08-2017 09:4443
      31-08-2017 09:1026

      Best Regards, Shivi

      • 257 Views
      • Categories: Actions & Filters, Calculations, Dates & Times
      • Tags:
        • 1. Re: Finding Last Time Stamp from Daily Data
          Simon Runc
          Simon Runc Ambassador Jan 6, 2018 9:45 AM (in response to Shivi Bhatia)

          hi Shivi,

           

          So this should do the trick for you.

           

          I created the following calculation, using a FIXED LoD

          [Last Record per Day]

          IF [Time of computation] =

          {FIXED DATETRUNC('day',[Time of computation]): MAX([Time of computation])}

          THEN [Total Cases] END

           

          Hope that is what you were after, and makes sense. Let me know if not.

          • 2. Re: Finding Last Time Stamp from Daily Data
            Shivi Bhatia
            Shivi Bhatia Jan 6, 2018 11:07 AM (in response to Simon Runc)

            Hi Simon,

             

            Thank you for your assistance. this has almost worked. I tried it with the data i had supplied and i get the below view.

             

            As you can see this shows NULL for any data point which is not the last value for that given day, i have placed last record field on filters and selected Special as "Non Null Values". This does the trick.

             

            Thank you for your time and help.

             

            Regards, Shivi

            • 3. Re: Finding Last Time Stamp from Daily Data
              Simon Runc
              Simon Runc Ambassador Jan 6, 2018 11:50 AM (in response to Shivi Bhatia)

              Cool, glad it helped.

               

              Yes that's what it's supposed to do! As it only populates the row that is the last entry, if you set the [Time of computation] to Day Level, and bring this field in...each day will just add up to the last record for each day. Just means that this field is additive (which can be useful).

               

              Equally if you only want the last record for each day

               

              Set up a calculation, like the below...bring it onto the filter shelf and set to true

               

              [Time of computation] = {FIXED DATETRUNC('day',[Time of computation]): MAX([Time of computation])}

               

              you can then just use the original cases field, as all the non-last record will be filtered out....always lots of way to solve a problem in Tableau!

              1 of 1 people found this helpful
              • 4. Re: Finding Last Time Stamp from Daily Data
                Shivi Bhatia
                Shivi Bhatia Jan 9, 2018 8:04 AM (in response to Simon Runc)

                Thank you Simon, this is very useful.

                 

                Really appreciate your help on this.

                 

                Regards.