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

# Finding Last Time Stamp from Daily Data

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.

 Time of computation Total Cases 23-08-2017 09:09 10 23-08-2017 09:39 6 25-08-2017 04:44 7 25-08-2017 04:14 9 25-08-2017 09:44 12 28-08-2017 05:44 14 29-08-2017 10:32 22 29-08-2017 10:55 31 31-08-2017 09:44 43 31-08-2017 09:10 26

Best Regards, Shivi

• ###### 1. Re: Finding Last Time Stamp from Daily Data

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

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

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

Thank you Simon, this is very useful.

Really appreciate your help on this.

Regards.