11 Replies Latest reply on Jan 3, 2016 11:20 PM by Rajeev Pandey

# How to Find Top N longest Running Jobs  Name?

Dear All,

Could you please help me in solving the below mentioned issue. I searched almost every thread in the Forum but none of the question has any similarity with my requirement.

Please throw some light on this .

Note : I am using Tableau Version 8.3

Requirement : How To find the Top N Longest Running Jobs name in the Specified Time Period . Time Period should contain Hours,Days,Weeks,Months,Years.?

I am having no idea how should I proceed in Tableau.

In addition to that, Also I need to find Top N jobs  with most incidents/Tickets created.

Attaching the sample Data for your reference.

I also followed the below approach but I dont know how to use the below concept with the current requirement.

Run Charts With Tableau

Rody ZakovichSimon Runc Shawn Wallwork Jonathan Drummey SMMC

• ###### 1. Re: How to Find Top N longest Running Jobs  Name?

Rajeev!

As a "rookie" i am taking the floor;)

I am on tableau 9.0 an on so I will try to explain.

So upfront! I am taking starting time as a basis for my Top N calculations.

What I did:

a. Defined : Start Time Hours based on Transform > Create Custum Date > Hours

b. Defined: Duration: DATEDIFF('minute',[START_TIME],[END_TIME])

c. Defined: Rank: rank(sum([Duration]))

d. Defined: Parameter based on START_TIME, Top N

e. Defined: Show?: [Rank]<=[Top N]

Hope it is clear....

1 of 1 people found this helpful
• ###### 2. Re: How to Find Top N longest Running Jobs  Name?

hi Rajeev,

So attached is the general way to do this. In the attached I've used the 'duration seconds' as the running time (although if this isn't correct a DATEDIFF on seconds between Start and End would calculate this in Tableau), and have used the 'Week End Date' as the 'Specified Time Period'.

So first I create my RANK calculation as follows

[Rank by Duration]

RANK(SUM([Duration Seconds]),'desc')

I then bring this field in and set to 'compute using' Job Name. So it Ranks each job, within each 'Week End Date'.

Next I create a parameter (so I can select the Top N) and then set the following calculation, to act as a filter as the parameter changes

[Show Top N]

IIF([Rank by Duration]<=[Top N],'Show','Hide')

I bring this into the filter shelf, ensure it's compute using is 'Job Name', and select Show.

Now as we change the parameter value the list of Job Names reflects this change.

You can do a similar thing with 'incidents/Tickets created' counts, and can change the 'Week End Date' to any time dimension you need.

Hope this helps, and if not the final solution gives you the tools to get to it.

1 of 1 people found this helpful
• ###### 3. Re: How to Find Top N longest Running Jobs  Name?

Dear Norbert Maijoor

First of all thank you so much for taking time and help me with my Stuffs, As I told you earlier also , these days I am learning so many things and technique from you. Thanks for sharing your Knowledge.I really appreciate that.

I would like to suggest something so that you can help other members as well.

1) Please keep a copy of Tableau 8.3 version we cant open Tableau 9.0

2) Or you can do some hacking and change the version of Tableau workbook from 9.0 to tableau 8.3

Re: Downgrade from 9 to 8.3

Downgrade version 9.0 to 8.3

In addition to that, I would like to know , do the I need to create an Integer parameter (START_TIME, Top N) .Because here I am getting the Date and Time Format. So do I need to perform custom Formatting  ( Like  Hourly 00 ) before creating the  Parameter right

• ###### 4. Re: How to Find Top N longest Running Jobs  Name?

Good morning Rajeev!

Hope you are doing well today!

As requested find attached the workbook in version 8.3

Hope it helps

• ###### 5. Re: How to Find Top N longest Running Jobs  Name?

Dear Norbert Maijoor

Thanks for posting the 8.3 version of tableau workbook.

It would be great if you can also help me in finding the next part of the question which is Top N jobs  with most incidents/Tickets created.

As We need to follow the same approach and perform the Ranking operation . DO i need to use"Number of Records" in text label

• ###### 6. Re: How to Find Top N longest Running Jobs  Name?

Rajeev!

Find updated workbook incl Top N jobname on tickets

• ###### 7. Re: How to Find Top N longest Running Jobs  Name?

Dear Norbert,

Thanks for this solution ,   I will check and update you accordingly/

In addition to that,Could you please share your Email id to me. I wanted to learn few techniques from you. if it sounds okay to you

the please drop me an email rajvivan11@hotmail.com . I shall ever remain thankful to you

• ###### 8. Re: How to Find Top N longest Running Jobs  Name?

Dear Simon Sir,

First of all , I would like to wish you a very happy new Year.May the coming year bring more happiness to you than last year. May you have an amazing year.

As per your description, it worked like a charm but now my user wants to see the same information top 5 longest running jobs  in the form of vertical bar Chart . As this chart contains all the dimension fields only , I am unable to change it into the bar chart . Whenever I am doing so, Show Top N filter  is become useless and its not showing any useful data at all. Can you please help me in getting the problem resolved.

Currently this is the approach I am following .Is there any other way that Can i use for representing my data . I means to say any other visualization that can fulfill my requirement

• ###### 9. Re: How to Find Top N longest Running Jobs  Name?

Hi Norbert,

Wish you a very happy new year, may god fulfill all your wishes.

I just wanted to know is there any possibility that we can represent the same information in the form of Vertical bar chart like top 5 longest Running job in the form of vertical bar chart. As this sheet contains all the dimension I am not able to create a bar chart . Please help me on this.

• ###### 10. Re: How to Find Top N longest Running Jobs  Name?

Rajeev!

Thank you! Appreciated. Wish  you also a very happy & healthy new year.

Find below my first attempt in the new year.

Something like this....?

Change the Marks from Text > Bar and bring the measure to Size. Remove measure from text.

Hope it helps...Please let me know.

Find my approach in attached workbook version 8.3

• ###### 11. Re: How to Find Top N longest Running Jobs  Name?

Thanks Norbert,

Yeah I followed the same approach but i want something more visualizing like better way to represent the data.Is there anything else you wanna suggest .

My approach was something like the below one