forgot to add that I can't figure out how to create the total number line and make it appear!
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.
total_break_out_line_edit.twbx 25.1 KB
THANK YOU SIR!
... seriously that was driving me nuts Joe thanks!
...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!
total_break_out_line_v2.twbx 10.4 KB
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?
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)
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)
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.
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.
Can you post your entire custom SQL you are using?
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])
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?