11 Replies Latest reply on Oct 28, 2015 7:39 PM by Peter Fakan

    How to change FY 20XX to appear as 20XX-20XX?

      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.

      Tableau Question Image.bmp

        • 1. Re: How to change FY 20XX to appear as 20XX-20XX?
          Mark Holtz

          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...FY calc.JPG

          1 of 1 people found this helpful
          • 2. Re: How to change FY 20XX to appear as 20XX-20XX?

            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"

            New Bitmap Image.jpg

             

            After changing fiscal year start to July, Tableau changes date header from "20XX" to "FY 20XX"

            New Bitmap Image.bmp

             

            Question: Can I change the look of the header from "FY 20XX" to something else such as "7/1/20XX to 6/30/20XX"?

            • 3. Re: How to change FY 20XX to appear as 20XX-20XX?
              sirajmandayippurath

              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

              1 of 1 people found this helpful
              • 4. Re: How to change FY 20XX to appear as 20XX-20XX?
                Mark Holtz

                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

                 



                • 5. Re: How to change FY 20XX to appear as 20XX-20XX?
                  sirajmandayippurath

                  Doh! How did I miss that one.. ??!!

                   

                  Thank you Mark!

                  • 6. Re: How to change FY 20XX to appear as 20XX-20XX?

                    Thanks to both of you. I'll give it a go next time I'm in Tableau and let you know how it goes.

                    • 7. Re: How to change FY 20XX to appear as 20XX-20XX?

                      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:

                      New Bitmap Image.jpg

                       

                      NOW: Line graphs greyed out

                      New Bitmap Image.jpg

                      • 8. Re: How to change FY 20XX to appear as 20XX-20XX?
                        Mark Holtz

                        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:

                        dates.png

                        1 of 1 people found this helpful
                        • 9. Re: How to change FY 20XX to appear as 20XX-20XX?
                          Jonathan Drummey

                          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.

                          1 of 1 people found this helpful
                          • 10. Re: How to change FY 20XX to appear as 20XX-20XX?

                            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

                            • 11. Re: How to change FY 20XX to appear as 20XX-20XX?
                              Peter Fakan

                              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.

                              blending.png