8 Replies Latest reply on Sep 10, 2018 3:13 PM by Gerardo Varela

# Calculate Time Difference between each hr

Hi Everyone,

My name is Manoj and i'm new to Tableau. I have a data set which has Date&time, client name and tickets purchased by them for each hour.

I have attached the sample data set below. My requirement is to list down top N clients based on each hour time stamp. Say if the present time is 11 AM.

In my dashboard, i wanted to display the Top N clients in the below-mentioned format (separate worksheets). Could you please suggest how to do the calculation.

 11:00 AM 9AM-10AM 7AM-8AM Top N Client Ticket Count Top N Client Ticket Count Top N Client Ticket Count Client B Client A Client C Client A Client B Client A Client C Client C Client B

Cheers Manoj

 Date & Time Clients TotalTicketes 9/9/2018 12:59:59 AM Client-1 100 9/9/2018 1:59:59 AM Client-1 121 9/9/2018 2:59:59 AM Client-1 110 9/9/2018 3:59:59 AM Client-1 32 9/9/2018 4:59:59 AM Client-1 234 9/9/2018 5:59:59 AM Client-1 234 9/9/2018 6:59:59 AM Client-1 543 9/9/2018 12:59:59 AM Client-2 200 9/9/2018 1:59:59 AM Client-2 300 9/9/2018 2:59:59 AM Client-2 10 9/9/2018 3:59:59 AM Client-2 32 9/9/2018 4:59:59 AM Client-2 234 9/9/2018 5:59:59 AM Client-2 390 9/9/2018 6:59:59 AM Client-2 123 9/9/2018 12:59:59 AM Client-3 3000 9/9/2018 1:59:59 AM Client-3 1200 9/9/2018 2:59:59 AM Client-3 10 9/9/2018 3:59:59 AM Client-3 11 9/9/2018 4:59:59 AM Client-3 543 9/9/2018 5:59:59 AM Client-3 32 9/9/2018 6:59:59 AM Client-3 21
• ###### 1. Re: Calculate Time Difference between each hr

You should be able to create a calculated field that gets the hour as follows:

DATEPART('hour', [Date & Time])

You can then build a view like this:

Is this what you're looking for?

• ###### 2. Re: Calculate Time Difference between each hr

Hi Ken,

Thank you for getting back, i would like to have three different worksheets.

Worksheet 1 for Top N clients for the current hour.

Worksheet 2 for Top N clients for the last two hours (Say now time is 11 AM, then the top N tickets sold by clients between 9AM -10 AM)

Worksheet 3 for Top N clients for the last two hours (Top N tickets sold by clients between 7AM - 8 AM)

Cheers,

Manoj

• ###### 3. Re: Calculate Time Difference between each hr

You'll need a handful of calculations for these. I've attached a workbook, which I hope will meet your need. It's driven by a parameter that allows the user to specify the "Current Hour." From there, it uses calculated fields to:

• Count the number of tickets for each of your three hour ranges.
• Determine which hours fit into each sheet.
• Filter each sheet to the hours required.

It then does a Top N filter for each sheet based on the appropriate measure (specific to the hour range). Finally, it sorts each sheet by that same measure.

• ###### 4. Re: Calculate Time Difference between each hr

Hi Ken,

Thank you for your quick replay. I'm using the older version of Tableu and could not open the worksheet which you have shared. Will it be possible for you to share the calculation online.

Cheers

Manoj

• ###### 5. Re: Calculate Time Difference between each hr

What version of Tableau are you using?

• ###### 6. Re: Calculate Time Difference between each hr

Hi Ken,

I use Tableau 10.0

• ###### 7. Re: Calculate Time Difference between each hr

Well, I can't convert it back that far, so here are the calculations.

Hour

// Hour of the record.

DATEPART('hour', [Date & Time])

Hour -1

// Current hour -1

[Current Hour]-1

Hour -2

// Current hour -2

[Current Hour]-2

Hour -3

// Current hour -3

[Current Hour]-3

Hour -4

// Current hour -4

[Current Hour]-4

Tickets Current

// Total tickets for this hour.

IF [Hour]=[Current Hour] THEN

[Total Ticketes]

ELSE

0

END

Tickets Current -2

// Total tickets for the previous 2 hours.

IF [Hour]=[Current Hour]-1 or [Hour]=[Current Hour]-2 THEN

[Total Ticketes]

ELSE

0

END

Tickets Current -2 -2

// Total tickets for the previous 3rd and 4th hours.

IF [Hour]=[Current Hour]-3 or [Hour]=[Current Hour]-4 THEN

[Total Ticketes]

ELSE

0

END

Current

// Does this record match the current hour selected?

IF [Hour]=[Current Hour] THEN

"Include"

END

Current -2

// Does this record match the one of the previous 2 hours?

IF [Hour]=[Current Hour]-1 or [Hour]=[Current Hour]-2 THEN

"Include"

END

Current -2 -2

// Does this record match the one of the previous 3rd or 4th hours?

IF [Hour]=[Current Hour]-3 or [Hour]=[Current Hour]-4 THEN

"Include"

END

• ###### 8. Re: Calculate Time Difference between each hr

Attached in version 9.0.

Regards,

Gerardo