Hello everyone
I'm in the process of creating some financial/fiscal year reports but I want to see if it's possible to change the appearance from the standard "FY 2012" format to something more along the lines of "2011-2012".
Is there a way to do this?
Thanks for reading
Shaun.
Are your date values actual date data types, or are they text values?
i.e., "FY 2012" or 9/11/2012 in your underlying data?
Assuming the values are text fields such as "FY 2010", then you could create a calculated field to "format" the [Activity Start Date] field by concatenated manipulated text strings.
1) RIGHT([Activity Start Date],4) will grab the 4 digit year from the end of the string
2) INT(RIGHT([Activity Start Date],4)) will convert the string to a number (so you can use the subtract operation)
3) INT(RIGHT([Activity Start Date],4))-1 will give you a numerical 4 digit year 1 less than the actual amount
4) STR(INT(RIGHT([Activity Start Date],4))-1) will turn the prior 4 digit year back into a string so you can concatenate it with a hyphen and the current year.
Altogether, it looks like this: STR(INT(RIGHT([Activity Start Date],4))-1) + '-' + RIGHT([Activity Start Date],4)
If your FY field is a date data type, we can always do the requisite date math...
Thanks for your response Mark.
Unfortunately, the headers (FY 20XX) are sourced from date data types. You can see from the image below how I displayed the header and graph in financial/fiscal years. This results in the FY 20XX format which I can't seem to display in any other date format.
As the target audience for my reports may be confused by what "FY 2012" represents, I want to change the header format to something else, "7/1/2011 to 6/30/2012" for example.
Before change - fiscal year start defaults to January - default format in header is "20XX"
After changing fiscal year start to July, Tableau changes date header from "20XX" to "FY 20XX"
Question: Can I change the look of the header from "FY 20XX" to something else such as "7/1/20XX to 6/30/20XX"?
Hi Shaun,
Would it work if you created a calculated field called header defined as the following:
'7/1/' + STR(DATEPART('year',[Order Date])) + ' to 6/30/' + STR(DATEPART('year',[Order Date]))
What it does here is to strip out the Year part of the date and then concatenate it with the strings to get to the display format you need. Would this help your visualisation? Hope it does.
Cheers
Siraj
Siraj was heading in the right direction, but you'll need a bit more.
If you want to display the labels as "7/1/2011 to 6/30/2012" you'll need to subtract 1 from the year so that the 7/1 portion uses last year, or add 1 if the 6/30 portion should use next year...
You can use an IF statement to determine if each date is past the FY cutoff:
IF DATEPART('month',[Order Date]) >=7 //use your FY month of cut-off here, if date in or after this month, adds next year )
THEN '7/1/' + STR(DATEPART('year',[Order Date])) + ' to 6/30/' + STR(DATEPART('year',[Order Date])+1)
// if date is before your cutoff, subtracts prior year
ELSE '7/1/' + STR(DATEPART('year',[Order Date])-1) + ' to 6/30/' + STR(DATEPART('year',[Order Date]))
END
Doh! How did I miss that one.. ??!!
Thank you Mark!
Thanks to both of you. I'll give it a go next time I'm in Tableau and let you know how it goes.
Thanks Mark,
Your advice worked. The only issue now is after I insert the calculation, Tableau no longer gives me the option to use line graphs which forces me to use bar graphs instead.
Does anyone have any ideas how I can change back to a line graph?
Thanks
BEFORE:
NOW: Line graphs greyed out
Well, the axis is no longer really continuous for "Calculation 1" since we are forcing a text string, so Tableau won't suggest a line graph as a "naturally intuitive" choice. (Before it was a date instead of this discrete text result).
I think you can still click on the drop down where "Automatic" appears on the Marks card and try changing to line graph.
One additional option would be to make the Calculation 1 IF statement return DATES instead of the strings.
I'd suggest using the starting DATE value of each fiscal year. It should be pretty obvious which year they're seeing because the next point will mark the start of the next year...
IF DATEPART('month',[Date]) >=7 // (use your FY month of cut-off here, if date in or after this month, adds next year )
THEN DATEADD('year',year([Date])-2000,#7/1/2000#) // (uses date in year 2000 as "start point" so subtracts 2000 to add only the difference)
//THEN '7/1/' + STR(DATEPART('year',[Date])) + ' to 6/30/' + STR(DATEPART('year',[Date])+1)
// if date is before your cutoff, subtracts prior year
ELSE DATEADD('year',year([Date])-1-2000,#7/1/2000#)
//ELSE '7/1/' + STR(DATEPART('year',[Date])-1) + ' to 6/30/' + STR(DATEPART('year',[Date]))
END
This image shows the difference in the results:
Sep 20, 2012 5:13 AM
Yes, you can choose your own Mark Type even when "Show Me" greys that out. The options in "Show Me" are Tableau's suggestions, the views they create can be duplicated by choosing specific Mark Types and having the proper layout of discrete and continuous elements in the view.
Thank you very much to everyone who responded, all very useful answers.
Mark, I followed your instructions and it all works perfectly now. I have yet to delve into the coding side of Tableau so thanks for giving me the answers on a silver platter.
Best regards
Shaun Boote
I realize this is an old thread, but its at the top of the google search on the topic of changing FY to something else.
We've been blending the data with a simple excel table that maps between unreadable and tableau readable data. All you do then in the background is load and link the columns you need to blend. This then gives you access to display a different title (such as Financial Year below) when you drop that field into the viz.
The benefits of this approach is you will continue to be able to use the line graph options as the dates aren't actually changing, just the display.
A better option OFC would be to go back to our Data Warehouse and get them to provide the financial year rollup, but this proved faster, coming in at about 10 minutes worth of work.
