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

    Visual Graphs -

    David Gabra

      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 -
          Don Wise

          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

           

          Screen Shot 2018-09-08 at 4.44.47 PM.png

          Screen Shot 2018-09-08 at 4.44.50 PM.png

          Screen Shot 2018-09-08 at 4.44.52 PM.png

          • 2. Re: Visual Graphs -
            David Gabra

            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 -
              David Gabra

              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 NameStatusDue DateRecommendaitonQuarterFiscal YearBusiness UnitRiskOpen/ClosedDefect Status
              AAIn Process31-Dec-171AQ22018GOMinorOpenExeeded
              AAIn Planning31-Aug-181BQ22018ITMinorOpenExeeded
              AAIn Planning31-Aug-182CQ22018ITMinorOpenExeeded
              CCDeffered31-Dec-171AQ22017ProcurementMinorClosedExeeded
              CCIn Process31-Dec-171AQ22017ProcurementMinorOpenExeeded
              CCIn Planning31-Dec-171BQ22016ProcurementMinorOpenExeeded
              DDIn Process31-Dec-171AQ32016GOMinorOpenExeeded

               

               

              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 -
                David Gabra

                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 -
                  David Gabra

                  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 -
                    David Gabra

                    I am embarrassed to ask

                    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 -
                      Don Wise

                      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 -
                        David Gabra

                        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 -
                          David Gabra

                          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 -
                            Don Wise

                            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 -
                              David Gabra

                              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 -
                                David Gabra

                                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 -
                                  Don Wise

                                  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

                                   

                                  Screen Shot 2018-09-14 at 10.12.29 AM.png

                                  • 14. Re: Visual Graphs -
                                    David Gabra

                                    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