13 Replies Latest reply on Jul 26, 2016 8:07 AM by Steve Martin

    How to combine 2 different charts?

    Vijay Kumar

      Please review the sample data in the attached file.

       

      I have Ticket numbers with Created Date and Resolved Date.

       

      I have created 2 different charts one for Created count for the particular month and the other for Resolved count on that particular month.

       

      Please see the screenshot from the sample graph I have created (Sample Chart 1).  Now I would like to combine this to one single graph as shown in sample screenshot  2. This is possible in Excel as we can enter manually to fill in data as below.

       

      Jan Created =1000 Closed =1500

      etc.,

       

      We can do a graphing on this but I am using tableau connected to SQL DB. 1st Graph = I have dragged Created On date --> Converted display to Month --> Dragged the number of records.

      Similarly 2nd Graph = I have dragged Resolved On date --> Converted display to Month --> Dragged the number of records.

       

      But I am not able to combine this to one graph since we are deriving Month field from 2 different column Created On and Resolved On

        • 1. Re: How to combine 2 different charts?
          Ivan Young

          Hi Vijay,

          If you can write sql one way to handle this is transforming your data and creating an event and single event date field.  Below is an example.

           

          From there you can put EventDate and Event in columns, sum records in rows and Event on colors.

           

          Good luck!

           

          Ivan

           

          select 'opened' as Event, CreatedDate as EventDate, id from table

           

          union

           

          select 'closed' as Event, ResolvedDate as EventDate, id from table

          • 2. Re: How to combine 2 different charts?
            Steve Martin

            Hi Vijay,

             

            I would recommend Ivan's solution with building this from the off in SQL but if this is not possible, you could always fall-back to blending using the date as your common field.


            Steve

            • 3. Re: How to combine 2 different charts?
              Vijay Kumar

              I am using a view from SQL DB. Is it possible to do it inside Tableau without touching DB?

              • 4. Re: How to combine 2 different charts?
                Steve Martin

                Is all of the data you require to complete this held in the view including a definition of open and complete?

                • 5. Re: How to combine 2 different charts?
                  Vijay Kumar

                  yes. I have Columns as below.

                   

                  ID

                  Created Date

                  Resolved Date

                  Status

                  Assigned To

                  • 6. Re: How to combine 2 different charts?
                    Steve Martin

                    I create a calculated field to determine the status:

                     

                    [Status] If IsNull([Resolved On (I)]) Then 'Open' Else 'Resolved' End

                     

                    And then from there created the chart.

                     

                    Though my numbers are a little different to your example.

                     

                    Steve

                    1 of 1 people found this helpful
                    • 7. Re: How to combine 2 different charts?
                      Vijay Kumar

                      Steve, I have just tried reviewing the attachment.

                       

                      Apologies for not making myself clear...

                       

                      My actual requirement is - When you count the number of tickets based on the Created On date's column the ticket count is 1613 (Only for Dec)and when you filter the excel using Resolved On date column for Dec the ticket count is 1248.

                       

                      This states we actually received 1613 tickets in Dec (Created) and 1248 tickets got resolved on Dec (But these resolved tickets are not only created on Dec it may have been created even before Dec. But got resolved on Dec month).

                      • 8. Re: How to combine 2 different charts?
                        Steve Martin

                        Apologies for this, though this is just as easy:

                         

                        two calcs:

                         

                        [# Open] Count([Created On])

                        [#Resolved]

                        Sum(

                        If DatePart('year',[Resolved On (I)]) = DatePart('year',[Created On])

                            And DatePart('month',[Resolved On (I)]) = DatePart('month',[Created On])

                        Then 1 Else 0 End

                        )

                         

                        Then plot.

                         

                        Steve

                        1 of 1 people found this helpful
                        • 9. Re: How to combine 2 different charts?
                          Vijay Kumar

                          Thank you so much for help

                          • 10. Re: How to combine 2 different charts?
                            lavanya koneru

                            Hi,

                             

                            In the trend chart,I would like to show every day when user goes on the line amounts shd change for each day.But in the botton only weeks are displayed as its more no of weeks.How can we acheive this.

                            • 11. Re: How to combine 2 different charts?
                              Steve Martin

                              Hi Lavanya,

                               

                              If I am correct in my understanding, you would like either a cumulative or rolling-sum based on each time your user logs into the system, much like a track of balances from a day-to-day operating of a bank account so instead of simply plotting the value that was added/subtracted, you want this value to be added/subtracted from the current figure?

                               

                              In which case either the running_sum or Previous_Value() functions will be more than sufficient

                               

                              Please can you clarify your problem and what it is you are trying to do.

                               

                              Also, please can you provide a clearer data-set with your workbook, in your post you have mentioned users though it is not clear from the data which field is defined as a User. Also, please can you expand the data-set to greater than two-days, try 7-14 days even if the data is false just so we have a greater field to work from and to be able to pick-up any errors from the outset.

                               

                              In the meantime, I have provided a link to a public workbook I have created to handle this, the calculations are commented: https://public.tableau.com/views/forumthread-198954/Plot2-ConnectedLines?:embed=y&:display_count=yes&:showTabs=y

                               

                              Steve

                              • 12. Re: How to combine 2 different charts?
                                Hussam Ahmed

                                hello steve

                                thanks for helping us here

                                i'm facing the same problem here. i have tried your solution as my date is exactly like Vijay's data

                                the problem is when we use the created date on column as the time sires i got the correct number of issues for open issues and the wrong number of issues for resolved one

                                 

                                i'm drilling down till week level cause i need them to be (the count of open issues vs resolved issues in that particular week of the current year)

                                 

                                im using this formula to get the number of resolved issues

                                 

                                sum((

                                If (      DATEPART('year',[RESOLUTIONDATE])    = DATEPART('year',[CREATED])and

                                          DATEPART('month',[RESOLUTIONDATE]) = DATEPART('month',[CREATED])and

                                          DATEPART('week',[RESOLUTIONDATE])   = DATEPART('week',[CREATED])

                                          and [calculated statues] = 'Resolved')

                                then 1 else 0   End))

                                How to combine 2 different charts?

                                could you please help how we could get the correct number of open and resolve issues and put it on a chart that has a pan for every week

                                thanks

                                • 13. Re: How to combine 2 different charts?
                                  Steve Martin

                                  Hi Hussam,

                                   

                                  Looking at it you may have a slight syntax error other than this, it is not immediately obvious as to why your calculation is not working. Adding the DatePart('week') function to my calculation for Vijay works as expected:

                                   

                                  I have re-written your above calculation to be syntactically correct -

                                   

                                  Sum(

                                       If Datepart('year',ResolutionDate) = Datepart('year',Created) And

                                            Datepart('month',ResolutionDate) = Datepart('month',Created) And

                                                 Datepart('week',ResolutionDate) = Datepart('week',Created) And

                                                      [calculated statues] = 'Resolved' Then 1 Else 0 End

                                  )

                                   

                                  If this does not help, please can you provide your data as an Excel file, this can be clean but at present I shall be working blind.

                                   

                                  Steve