Can you provide some sample data?
Hi this counts the status not the instances a ticket is created per month so i would need to have count of instance of Id in date/month and then second axis amount of status = "Closed" in that same month
Have you tried creating two calculated fields?
Calculated field 1:
IF Status = "Open"
IF Status = "Closed"
Now put these two calculated fields in your view and aggregate them on CNTD so get a distinct count.
Now dual axis them and you should be done.
i understand but would that calculate the amount of tickets opened not open in that month so basically im looking for a count of the amount of tickets opened per month. The count of status is easy and combining the axis as dual is fine but how can i count the amount of distinct . In other words in basic format
Tickets opened in January: 56
Tickets closed in January 54
Do i count the unique Ticket id number and total them per month?
Wayne - Number of open items over time may well be the most complex common use case! There are a couple different ways to address it, but the best place to start is probably the Open & Close Dates FAQ.
If you're only interested in counting open and close activity, that would be much more straightforward, as long as you have an Open Date and a Close Date (ideally each on its own row--the data reshaping techniques in the FAQ can help you get there). It doesn't look like you have a Close Date in your data, though, unless you can safely assume Close Date is IF [Status]="Closed" THEN [Updated].
If that's the case, then you can create two measures ([Open/Close] being a field that indicates whether the row contains an Open Date or a Close Date--remember, each on its own row):
Tickets Opened: IF [Open/Close] = "Date Opened" THEN 1 END
Tickets Closed: IF [Open/Close] = "Date Closed" THEN 1 END
Then, all you have to do is plot both measures against your [Open/Close Date] field--no need for a dual axis!
Please see below i simply want to know how many times in a month period was a ticket created ie count of ticket ID's or date instances in the one month. Its a very basic excel calculation. Then i want to cross that with amount of closed status in that same month so the report might look like below:
I do not need to count status the status. I need to count the instances that a ticket was created in a each month as in there were 60 tickets created in December. Then on the same chart count the values of the closed status for the same period hence giving me the KPI for amount of closed tickets vs the amount opened (not open) per month. The problem is when i count say the measure for the unique ID no for each ticket created i get my "Tickets opened" value but when i add a count for tickets closed per month the status changes everything to count for only that filter and there is not a possibility for a dual axis which i require.
I too am trying to find a solution to this in Tableau as well.
I have records with both Opened_at and Closed_at date/time fields in them.
I need to see side by side comparison of those records Opened_at in January against those records Closed_at in January.
Just to make it complicated, I have 3 types of tickets in my data sheet that I need to stack in that bar chart.
Any assistance would be appreciated.
Apologies for late reply.
Had you found a solution?
If not, please see if the attached v10.0 could be useful.
It requires pivoting the Open and Close dates into one column.
226914openclose.twbx 1.2 MB