Help with Calculated Fields and Dashboard visualization

Current problems to solve:

1.     Context – My dashboard obviously doesn’t have the exact data used for work due to privacy, but I tried to fill in as much data as close to the original dashboard workbook that I use.

2.     Please see my Tableau dashboard with calculations, Excel workbook with data, a screenshot of the criteria I am trying to use, and finally the Excel I created while referencing online sources on calculations in Excel.

The dashboard I created was based on the Project Management Data that the project managers or leads update frequently in another software. The PM meets with management every 2 weeks to discuss any problems with the project and if there are any blockers.

Keep in mind that some of the people running the team meetings are more familiar with Excel and other formatting tools and not as much on Tableau and how it works..

(attached screenshot on the view we are trying to build)

What we are trying to accomplish:

Show a viz with all the project data fields and create a calculation that shows the projects that need review. On the tool tip - show additional information for the project data.

First, the review required field should filter out the projects that need review vs the ones that are good. This is based on a calculation further discussed below.

1.     The “Review Required” calculation for the dashboard is as follows:

If the projects are in a particular group – show any projects that are not ‘On Track’ (i.e.: Monitor, At Risk, No Rating for any of the 3 fields: Scope Health, budget Health, and Schedule Health)

• in other words: if there is a no rating(null), monitor or at risk for any of the fields this will automatically put the project at needs ‘Review’
• If the project does not have a start/finish date (i.e., Null), does not have any updates filled in the 3 sections: General Updates, Key Accomplishments, and Key Risks (i.e., Null). Also, if the projects have been updated more than 14 days then the project data is considered ‘aging’ ‘stale’.

The meeting takes place twice in the month – on the first and on the 15th of each month. The team needs to make sure the project data is updated in less than 2 weeks.

2.     In the tooltip – show all the project fields: Project ID, Scope health, Schedule Health, and Budget Health, Start Date, Finish Date, and Last Updated ( in days, weeks, months, or years)

• a.     The Last Updated field must be shown in days, weeks, months, or year format. Most preferred: how many days or weeks ago was this updated? see Excel.
• Additionally, if this is shown in days then hide weeks and years, if this is shown in weeks then add days and hide the rest, if this is shown in years then add how many days, weeks, and years altogether.
• b.     I have been trying to come up with the values and calculations but failing miserably because Tableau does not do the same calculations as Excel for DATEDIFF.

3.    Also, If the project fields under General Updates, Key Accomplishments, and/or Key Risks seem to have a Space in the data and not Null and currently this calculation should throw an exception to review the project instead it shows as the review is not required.

How do I fix this?

4.     Lastly: If there is a way to show tooltip for all columns instead of just the last one that would be helpful. (Lowest priority) 

Calculated Fields

Review Required:

NOT

(Not[Scope Health] = "On Track" or [Schedule Health]= "On Track" or [Budget Health] = "On Track"

AND

(Not[Business Unit (group)] = "B" AND [DateDiff]>14))

OR ISNULL([Start Date])

OR ISNULL([Finish Date])

OR ISNULL([General Updates])

OR ISNULL([Key Accomplishments])

OR ISNULL([Key Risks])

// currently this statement looks at the project fields that need review

// Result: Yes requires review and no does not require review.

//T/F or Y/N: Yes, means needs review, no means does not require review.

Initially, this calculated if any of them were in the 'No review required' category but my sponsor asked me to change it to: "NEEDS REVIEW" so had to reverse the calculation to allow the opposite. It works so didn't change the formula.

DATEDIFF:

DATEDIFF('day',[Last Updated],Today())

How do I show the difference is in days, weeks, months, and/or years?