In your screenshot, the date fields are showing as a string, when they should be set as a date field. Click the "Abc" label at the top of the field and change it to "date" and you'll likely have better luck making your Viz. If not, upload a copy of the workbook and we can see what we can do.
You could build a calculated date field using the "Year" and "Month" dimensions. The formula below should work. This assumes that you are working with two string dimensions (year and month). The formula just converts the month string to a number string, then concatenates it with '01' for a day value and then concatenates the year value. The whole thing is wrapped in a DATE() function to convert the result to a date data type. Hope this helps!
WHEN "Jan" THEN "01"
WHEN "Feb" THEN "02"
WHEN "Mar" THEN "03"
WHEN "Apr" THEN "04"
WHEN "May" THEN "05"
WHEN "Jun" THEN "06"
WHEN "Jul" THEN "07"
WHEN "Aug" THEN "08"
WHEn "Sep" THEN "09"
WHEN "Oct" THEN "10"
WHEN "Nov" THEN "11"
WHEN "Dec" THEN "12"
This is great, thank you!
Just wondering, do the number strings have any significance on the fiscal year start date? Our Fiscal Years starts in August, so is it necessary to rewrite the about function with Aug starting at 1, or does changing the fiscal year start date in Tableau (right click, fiscal year start date, August) have the same effect?
Edit, so it looks like if I do change the above function starting with Aug as 1, then leave the Fiscal Year start date at January, I get correct values.
Anyways, thanks again everyone!
The formula will generate the actual date, then the power of tableau will do the conversion to fiscal years by assigning the fiscal year start date by right clicking as you described.
Hmm ok, sorry one more thing. SO in my workbook, the data is already calculated for the fiscal date.
For instance, in the underlying data, August 2015 is actually shown as:
- Year: 2016
- Quarter: 1
- Month: Aug
So using your calculated field, I am showing values for 2017, because it is calculating the fiscal year again when it was already done in the underlying data.
Let me know if this doesn't make sense.
test.twbx 748.4 KB
I've never actually used the fiscal date functionality, so hopefully someone who has used it can hop on this thread to help clarify. Glad we could at least get you a valid date field to work with!
I have used the fiscal date function a lot to cope with companies that do not have a calendar financial year.
Once you have your date information as normal dates then right click on the Date field under dimensions.
Select Default properties|Fiscal Year Start and then the month that your year starts.
Months will still show normally but quarters will align with the fiscal year start that you have chosen. It is a really cool feature of Tableau