8 Replies Latest reply on Sep 10, 2018 12:23 PM by Deepak Rai

# Calculate the time difference and list the top N clients

Hi Everyone,

My name is Manoj and i'm new to Tableau. I have a data set which has Date&time, client name & 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.

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)

Regards,

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 the time difference and list the top N clients

Hi Manoj,

Thanks

Deepak

• ###### 2. Re: Calculate the time difference and list the top N clients

Hi Manoj,

Here is one example.

Create parameter for top N (I just created 1 and 2 only)

Label Change

Filter for respective worksheet

0 is for current

1 is for last hour

2 is for two hours before.

No display filter.

You can replace parameter of "Instead of current time" with now() in real environment.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 3. Re: Calculate the time difference and list the top N clients

Hi Deepak,

Thank you for getting back. Sorry i'm using older version of Tableau (10.0) and cant open the file you have shared. Could you please share the screen shots of the calculation. It will be really helpful.

• ###### 4. Re: Calculate the time difference and list the top N clients

Latest

Drag all the above calcs to Filter, Add them to Context by Right Click and Select TRUE.

Thanks

Deepak

• ###### 5. Re: Calculate the time difference and list the top N clients

Hi Deepak,

Really appreciate it. Will cross check and get back to you.

• ###### 6. Re: Calculate the time difference and list the top N clients

Hi Shin,

• ###### 7. Re: Calculate the time difference and list the top N clients

Hi Deepak,

What i'm looking for is, at the present hour who are the top N clients by tickets sold. Based on the present hour, i wanted to check the previous hour top N client by tickets sold.

So each time, i will check my current hour and wanted to display my previous hour's data.

Thanks & Regards,

Manoj

• ###### 8. Re: Calculate the time difference and list the top N clients

Isn't it the same what I did? Please check your Initial description. For Present 11, you want to see only 9-10 and 7-8. Let me know, with Present 11 as Latest, what should be the time period for other two sheets. I can modify.

Thanks

Deepak