Why don't you create calculation for # Open in Jan and set it to IF [Open Date] <=Date("31/01/2016") AND [Closed] >Date("31/01/2016") THEN 1 ELSE 0 END
and similar for # Open in February
Hi Kristie -
Not sure I fully understand your need but what happens if you reverse the IF tests - that is test first to check those that were still open after Feb, then check to see what was open in Jan -
No this wont work for the same reason - There is a possibility that there are records on in both Jan and Feb, and i need it counting twice but i cant seem to find away to do this.
Here are the figures, i have worked them out using excel using the COUNT IF Function -
i want to recreate this in tableau using 1 calculated field
Can you show some sample data so we can understand your data better and is it posible that one can be opened, closed and re-opened etc. If you can prepare your data in the long format, it would be much easier. ie
ID DATE STATUS 20011 1/1/2016 OPEN 20012 1/2/2016 OPEN 20013 1/3/2016 OPEN 20014 1/4/2016 OPEN 20015 2/5/2016 OPEN 20012 2/2/2016 CLOSE 20013 2/3/2016 CLOSE 20014 2/4/2016 CLOSE 20015 2/5/2016 CLOSE
Not certain this totally meets your need but you can try looking at the live training video at - Live Training Resources | Tableau Software
The one you want to look at is the "Data Blending Recorded Training -(note it is a recording of a live training session) - the last 5 minutes deal with ;your issue
See the attache workbook - I just made a simple example of records with open and close date - over a 6 month period
The key to solving the problem is to make a duplicate of the data file after you have loaded it into tableau then creating a relationship between the open date in one file and the close date in the other file.
The workbook table the creates a running total of the open date - a running total of the close dates and the difference (which is the net open records)
Does that meet your need?
Count of opens.twbx 26.3 KB