6 Replies Latest reply on Aug 2, 2016 9:33 AM by Jin Wu

Hi I have two sets of date in one excel file, from 7/1/2014 to 6/30/2016. I work at a university which academic year starts on 7/1. When I try to compare two academic year, 7/1/2014-6/30/3015 and 7/1/2015-6/30/2016, for example, drag the date to the "color", it shows 2014, 2015, and 2016. How can I only compare two time range instead of three?

I created a calculated field as below, but it's not working.

If[Date]>=DATE(7/1/2014)AND [Date]<=DATE(6/30/2015) THEN '2014-2015'

ELSEIF [Date]>=DATE(7/1/2015) AND [Date]<=DATE(6/30/2016) THEN '2015-2016'

END

• ###### 1. Re: academic year problem

Why isn't it working? Are you getting an error? Is your data wrong?

You will need to provide more details and a Tableau Packaged Workbook including some sample data.

Thanks

• ###### 2. Re: academic year problem

Hi Tom,

Thank you for helping me. I attached the workbook. The calculated field I created is called "Date Range". I dragged Date Range to either Columns or Color. Both showed NULL. I would appreciate it if you could take a look at it.

-Jin

• ###### 3. Re: academic year problem

Hi Jin,

The only thing to add is a single quotation mark (') inside the date brackets.

This worked for us.

If [Date]>=DATE('7/1/2014')AND [Date]<=DATE('6/30/2015') THEN '2014-2015'

ELSEIF [Date]>=DATE('7/1/2015') AND [Date]<=DATE('6/30/2016') THEN '2015-2016'

END

Let us know.

Chris

• ###### 4. Re: academic year problem

HI Jun Wu,

Please find the attached screenshot. This field should be dynamic in nature. You have wrote dates in the calculated fields. If you use it you will have to update that calculated filed every Academic fin year. Below calc will solve your problem. Attaching tableau workbook 9.3 as well as excel data that I have used. You can use it along with day granurality as well.

if MONTH([Dates])>=7 and MONTH([Dates])<=12 THEN str(YEAR([Dates]))+"-"+STR(YEAR([Dates])+1)

elseif MONTH([Dates])>=1 and MONTH([Dates])<=6 THEN str(YEAR([Dates])-1)+"-"+STR(YEAR([Dates]))

END

Let me know if this helps.

Thanks and Regards,

Ashish Chaudhari

• ###### 5. Re: academic year problem

Thanks Chris! It works! I appreciate your help!

-Jin

• ###### 6. Re: academic year problem

This is awesome!! Thanks so much for helping me on this. I appreciate it.

-Jin