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.
select 'opened' as Event, CreatedDate as EventDate, id from table
select 'closed' as Event, ResolvedDate as EventDate, id from table
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.
I am using a view from SQL DB. Is it possible to do it inside Tableau without touching DB?
Is all of the data you require to complete this held in the view including a definition of open and complete?
yes. I have Columns as below.
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.
198954 - combine data.twbx 165.4 KB
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).
Apologies for this, though this is just as easy:
[# Open] Count([Created On])
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
198954 - combine data.twbx 186.9 KB
Thank you so much for help
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.
Graph.twbx 29.6 KB
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
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
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))
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
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 -
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.