1 2 Previous Next 18 Replies Latest reply on Oct 7, 2018 2:22 AM by David Gabra

# Visual Graphs -

Hello

I have tried with you tube videos and website to try and teach myself, but with no real success, and what I need seems is so simple for me in excel and but not in Tableua

I need to produce a few graphs based on my data - please can you help me on this. Once I have the template I should be able to adapt and take it going forward.

I am attaching a .twbx file  and the original excel.

Note

All recommendations have a  due date status.

exceeded due date or before due date  or extended .

Based on the current day:  FYI

If the recommendation IS open by  due date -  and the due date has passed - the recommendation status should be 'exceeded due date'

If the recommendation IS open by due date -  and the due date has NOT PASSED - the recommendation should be'before due date'

there are special cases - where there is an extension and so it has status of  extended.

Graphs Needed - Examples below.

Note - issues  = recommendations

1. Open Recommendations by FY    [field name = Fiscal Year]  +  [due date status]

Example of how it should look  - number not accurate -just for visualizations

2. Open Recommendations Ranking by Risk and Due Date   [field name  = Risk] and [field name  = due date status ] +   Open/Closed

Each recommendation will have a rating and will have a due date status.

Example of how it should look  - number not accurate -just for visualizations

3. Percentage of Open Recommendations by Report Issue Date   [field name  = Open/Closed]   +  Fiscal Year  + Quarter]  +

Note - percent of open - is total open from the total per project - this needs a calculated field. - I could not do this.

I spent 2 hours putting this together - hope it is clear.

thank you for whoever volunteers to help.

David.

• ###### 1. Re: Visual Graphs -

Hello David,

If I read your instructions correctly, the attached and below screenshots should meet your needs.  I'm assuming that your Due Date Status is a field that you supply and is not a calculation.  Thx, Don

• ###### 2. Re: Visual Graphs -

Hi

does that make a difference? - where would I put that

Due Date

would be a calculated field.

if [rec due date]>Today()  then 'still have time'

elseif [rec due date]<=Today()  then 'time Passed'

end

• ###### 3. Re: Visual Graphs -

Hi

Please see below for points - (sorry if I was not clear)

For the example -

1) the minor exceeded should should show only 6 (since we are only interested in open items)

- you show 7 and you also show  a breakfront on 5 and 2 - I do not understand the where the 5 and 2 come from?

I guess you can remove the numbers within. - just have a top total.

see raw data from excel if you filter for minor and exceed the due data

 Project Name Status Due Date Recommendaiton Quarter Fiscal Year Business Unit Risk Open/Closed Defect Status AA In Process 31-Dec-17 1A Q2 2018 GO Minor Open Exeeded AA In Planning 31-Aug-18 1B Q2 2018 IT Minor Open Exeeded AA In Planning 31-Aug-18 2C Q2 2018 IT Minor Open Exeeded CC Deffered 31-Dec-17 1A Q2 2017 Procurement Minor Closed Exeeded CC In Process 31-Dec-17 1A Q2 2017 Procurement Minor Open Exeeded CC In Planning 31-Dec-17 1B Q2 2016 Procurement Minor Open Exeeded DD In Process 31-Dec-17 1A Q3 2016 GO Minor Open Exeeded

also for the due date'

Due Date  - the Defect status field

would be a calculated field.

if [rec due date]>Today()  then 'still have time'

elseif [rec due date]<=Today()  then 'time Passed'

or if rec date = 21/01/2024 - then "extended"

I feel we are nearly there

• ###### 4. Re: Visual Graphs -

Due Date  - the Defect status field

would be a calculated field.

if [rec due date]>Today()  then 'still have time'

elseif [rec due date]<=Today()  then 'time Passed'

or if rec date = 21/01/2024 - then "extended"

I added a condition for extended - the data I gave you is dummy data  - if we can change the due dates for extended to this '21/01/2024' - then it should work.

thank you *1000 for all the help.

• ###### 5. Re: Visual Graphs -

Hi

I had difficult opening the file since it was in a newer version.

All seems good for the total number - can you please just let me know what the number in the bar represent and how I can remove them if needed.

you can ignore all the above and just pay attention to this.

thanks!!!!!

David.

• ###### 6. Re: Visual Graphs -

Is there any way of making this

resolved-= closed

we see that not started + planning + process  = to open.

this is high level summary in table.

If you can think of a way to add the risk so it looks presentable - that will be awesome!!!!!

• ###### 7. Re: Visual Graphs -

Hi David,

What version of Tableau are you using? I'll work on the other questions.  The first question however is simply due to providing all data without filtering out open/closed, which is why the numbers are different.  I'll add an open/closed filter which will then come into alignment for what you expect.  Thank you for the clarification on 'extended' as that would be my next question.  I'll post a new workbook shortly.  Thx, Don

• ###### 8. Re: Visual Graphs -

Hello

Let me say I really really appreciate your help.

I just updated to the latest version of Tableau so we should be okay.

Kind reminder if you can post the new workbook.

1. Can we make the following amendment for the calculated field for due date:

if [due date]>Today() and [open/close]='open' then 'before due date'

or

if [due date]<=Today() and [open/close]='open then 'exceeded due date'

elseif [due date]=12/31/2022 and [open/close]='open' then 'exceeded'

2.

For the summary I have added a screenshot of how it should look.

Since in the real data I have 100s of lines if takes me ages. If this could be automated it would be a g-d sent.

thank you

I have added an updated excel that reflects what you see below. - if you can ammend to the original tableua file so we have all on

one file that would be amazing -

thank you so much.

• ###### 9. Re: Visual Graphs -

Hi

I attached an excel file with the updated data.

thank a million.

David.

Copy of the post - so you have all on one page.

Let me say I really really appreciate your help.

I just updated to the latest version of Tableau so we should be okay.

Kind reminder if you can post the new workbook.

1. Can we make the following amendment for the calculated field for due date:

if [due date]>Today() and [open/close]='open' then 'before due date'

or

if [due date]<=Today() and [open/close]='open then 'exceeded due date'

elseif [due date]=12/31/2022 and [open/close]='open' then 'exceeded'

2.

For the summary I have added a screenshot of how it should look.

Since in the real data I have 100s of lines if takes me ages. If this could be automated it would be a g-d sent.

thank you

I have added an updated excel that reflects what you see below. - if you can ammend to the original tableua file so we have all on

one file that would be amazing -

thank you so much.

• ###### 10. Re: Visual Graphs -

Hi David,

I will give you what I have done (2018.2 version is newly attached) up until now and will then look at the new data.  I probably won't be able to get back to you on this until Thursday at earliest, maybe Friday due to other obligations.

See if what I've already done meets what you're looking to do?

Please remember that Tableau is not MS-Excel, but I will do what I can to mimic what you've displayed!

Thx, Don

• ###### 11. Re: Visual Graphs -

Thank you!!!!!!! any time this week is brilliant - you have no idea, if it works, how much time it will help.

I so appreciate your kind help.

David.

• ###### 12. Re: Visual Graphs -

Hi Don,

What you have done so far is amazing. We are very near the end :-).

Do you think you will have time by EOW to complete.

Thank you kindly

David.

• ###### 13. Re: Visual Graphs -

Hi David,

Please see newly attached workbook (2018.2) with your updated data and latest requested changes.  It's about as far as I can get with your MS-Excel example.  Hope it helps.  If this resolves your issue, please close this thread by marking as correct.  Thx!  Don

• ###### 14. Re: Visual Graphs -

Hi

You are truly amazing - thank you.

I have been going through how you did it - trying to learn from you - and I have an question -

1. - What the square mean

2. what is difference between the two data sources below - which should I use. I want to connect to the SharePoint and I want to know which to use. Are they both used, and what is the purpose?

thanks

David

1 2 Previous Next