6 Replies Latest reply on Dec 16, 2016 10:23 AM by Jim Dehner

# How to get tableau to use whole data set on IF/ELSEIF function or something similar

Hi All

I apologies if this sound confusing.

I have a data set with Open And Closed Dates

I want to know how many records where open at the end of January, irrelevant of if they are now closed. My calculated field is this:

IF [Open Date] <=Date("31/01/2016") AND [Closed] >Date("31/01/2016") THEN "Open in Jan" ELSE "other" END

Now i know that this will bring back only records where it is closed (this is fine) but what i want to do is extend this calculation to tell me also how many records where also open at the end of February. for example

IF [Open Date] <=Date("31/01/2016") AND [Closed] >Date("31/01/2016") THEN "Open in Jan"

ELSEIF  [Open Date] <=Date("29/02/2016") AND [Closed] >Date("29/02/2016") THEN "Open in Feb" Else Other END

Now this calculation works fine for JAN but the records that don't fall into the JAN category will then go through the FEB calculation, where as i need the whole data set to go through the FEB calculation.

Is this possible to do in 1 calculated field or is it not possible?

Unfortunately i am unable to attach any workbooks or examples due to the content of the data i am using

• ###### 1. Re: How to get tableau to use whole data set on IF/ELSEIF function or something similar

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

• ###### 2. Re: How to get tableau to use whole data set on IF/ELSEIF function or something similar

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 -

• ###### 3. Re: How to get tableau to use whole data set on IF/ELSEIF function or something similar

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

Here are the results from doing the calculation opposite (eg. Feb First then Jan)

• ###### 4. Re: How to get tableau to use whole data set on IF/ELSEIF function or something similar

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

• ###### 5. Re: How to get tableau to use whole data set on IF/ELSEIF function or something similar

Found this post. Is it similar to your quesion?  combine two date fields on same axis

• ###### 6. Re: How to get tableau to use whole data set on IF/ELSEIF function or something similar

Kristie

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)