1 2 Previous Next 18 Replies Latest reply on Mar 31, 2011 10:17 AM by . Jasonz

    need help with line graph

    . Jasonz

      Heya Folks,

       

      I am trying to create a line graph that has 3 lines on it: open, closed and the total number... I have attached a packaged workbook. Any help would be great!

       

      JZ

        • 1. Re: need help with line graph
          . Jasonz

          forgot to add that I can't figure out how to create the total number line and make it appear!

          • 2. Re: need help with line graph
            Joe Mako

            You have a variety of options available to you.

             

            Attached is two:

            - using a dual axis with multiple mark types, and a different dimension on the color shelf to set a different aggregation level.

            - custom SQL, duplicating the data, and in one copy setting the value of the color shelf.

             

            I generally prefer the second one because it give you a single axis, and single color shelf.

            • 3. Re: need help with line graph
              . Jasonz

              THANK YOU SIR!

              • 4. Re: need help with line graph
                . Jasonz

                ... seriously that was driving me nuts Joe thanks!

                 

                JZ

                • 5. Re: need help with line graph
                  . Jasonz

                  ...got an addition asked of me for it (of course)... now I have been asked if its possible to bring older tickets that were resolved this year but opened last year and include it in the resolved of this year line.

                  so with the attached workbook is there a way to combine the resolved in 2011 with tickets opened in 2010, keeping in mind that the total line from before must reflect only tickets opened in 2011? Basically it is a graph showing all tickets opened in 2011 vs all tickets closed in 2011 (regardless of the day they were opened)

                   

                  Thanks again for all the help!

                   

                  JZ

                  • 6. Re: need help with line graph
                    Joe Mako

                    There are few ways to do this, and depending on what you are looking for, or the logic you want to apply, the results can be different. Attached is one option that uses custom SQL.

                     

                    Are these the results you would expect from this sample data? If not, what values would you expect to be the result?

                    • 7. Re: need help with line graph
                      . Jasonz

                      hmmm not exactly, the closed from Jan 2010, Feb 2010 and march 2010 need to be combined into the month they were actually closed, so Feb 2011 should show 4 closed (2 reported in 2010 and 2 from 2011)

                      • 8. Re: need help with line graph
                        Joe Mako

                        Yes I had botched the SQL in the previous comment. Here it is resolved, sorry about that.

                        • 9. Re: need help with line graph
                          . Jasonz

                          thats the look I want, one more question can you do this with the dual axis option you gave earlier? I am just trying to make a one refresh extract report. If its not possible thats ok too (please let me know)

                           

                          Thanks!

                           

                          JZ

                          • 10. Re: need help with line graph
                            Joe Mako

                            No, it will have to be a different connection because your raw data you provided in v2 is at the ticket level of detail (I cannot tell what level of detail your first example data set is), and you need to connect to a data source that is at the action level of detail to generate the view you are requesting. So instead of one row per ticket, you need one row per action. That is what the custom SQL is doing.  The shape and level of detail of source data you want connect to depends on the business logic you want applied, and what calculations you perform, different structures allow for different questions to be answered in Tableau.

                            • 11. Re: need help with line graph
                              . Jasonz

                              Hi Joe,

                               

                              Is there another statement other than "open" as [action] (all of the as [action] lines) I can use?  my SQL query kicks back:

                               

                              Database error 0x80040E14: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

                              Database error 0x80040E14: Invalid column name 'Open'.

                               

                              If I remove the lines it querys but not with it.

                               

                              Thanks,

                               

                              JZ

                              • 12. Re: need help with line graph
                                Joe Mako

                                Can you post your entire custom SQL you are using?

                                • 13. Re: need help with line graph
                                  . Jasonz

                                  Here  you go:

                                   

                                  SELECT [Ticket_Investigation].[Ticket_Investigation_SID] AS [Ticket_Investigation_SID],

                                    [Ticket_Investigation].[Ticket_Investigation_ID] AS [Ticket_Investigation_ID],

                                    [Ticket_Investigation].[Submit_DateTime] AS [Submit_DateTime],

                                    [Ticket_Investigation].[Description] AS [Description],

                                    [Ticket_Investigation].[Product_Categorization_Tier_1] AS [Product_Categorization_Tier_1],

                                    [Ticket_Investigation].[Product_Categorization_Tier_2] AS [Product_Categorization_Tier_2],

                                    [Ticket_Investigation].[Product_Categorization_Tier_3] AS [Product_Categorization_Tier_3],

                                    [Ticket_Investigation].[Categorization_Tier_1] AS [Categorization_Tier_1],

                                    [Ticket_Investigation].[Categorization_Tier_2] AS [Platform],

                                    [Ticket_Investigation].[Categorization_Tier_3] AS [Categorization_Tier_3],

                                    [Ticket_Investigation].[Assigned_Group] AS [Assigned_Group],

                                    [Ticket_Investigation].[Target_Resolution_DateTime] AS [Target_Resolution_DateTime],

                                    [Ticket_Investigation].[Investigation_Justification] AS [Investigation_Justification],

                                    [Ticket_Investigation].[Assigned_Group_PM] AS [Assigned_Group_PM],

                                    [Ticket_Investigation].[Assignee_PM] AS [Assignee_PM],

                                    [Ticket_Investigation].[KE_Created_DateTime] AS [KE_Created_DateTime],

                                    [Ticket_Investigation].[Detailed_Description] AS [Detailed_Description],

                                    [Ticket_Investigation_Status].[Enumeration_ID] AS [Enumeration_ID],

                                    [Ticket_Investigation_Problem].[Responsibility] AS [Responsibility],

                                    [Ticket_Investigation].[Value_Mapping] AS [Priority],

                                    [Ticket_Investigation].[Last_Modified_DateTime] AS [Last_Modified_DateTime],

                                    [Ticket_Investigation_status_Reason].[Value_Mapping] AS [StatusReason],

                                    [Ticket_Investigation_association].[Request_Type_01_Value] AS [Request_Type_01_Value],

                                    [Ticket_Investigation_Status].[Value_Mapping] AS [Status]

                                  FROM [dbo].[Ticket_Investigation] [Ticket_Investigation]

                                    LEFT JOIN [dbo].[Ticket_Investigation_Status] [Ticket_Investigation_Status] ON ([Ticket_Investigation].[Investigation_Status_FK] = [Ticket_Investigation_Status] .[Enumeration_ID])

                                    LEFT  JOIN [dbo].[Ticket_Investigation] [Ticket_Investigation] ON ([Ticket_Investigation].[Priority_FK] = [Ticket_Investigation_priority].[Enumeration_ID])

                                    LEFT JOIN [dbo].[Ticket_Investigation_association] [Ticket_Investigation_association] ON ([Ticket_Investigation].[Ticket_Investigation_SID] = [Ticket_Investigation_association].[Association_From_Request_FK])

                                    LEFT JOIN [dbo].[Ticket_Investigation_status_Reason] [Ticket_Investigation_status_Reason] ON ([Ticket_Investigation].[Invesitgation_Status_Reason_FK] = [Ticket_Investigation_status_Reason].[Enumeration_ID])

                                  • 14. Re: need help with line graph
                                    Joe Mako

                                    I don't see any issue in that SQL or the  "open" as [action] line.

                                     

                                    I apologize for not being clearer. Can you please post the SQL you are using that is generating the error?

                                    1 2 Previous Next