8 Replies Latest reply on May 30, 2017 12:54 PM by Joe Oppelt

    FY to Date comparison based on Parameter Month

    Chris Rosado

      Hi,

       

      Looking for a calculated field or other method to create a bar chart that shows sum of sales to date based on the Month Parameter.

       

      The attached sample workbook has what I have so far. I pretty much made this workbook just to show why I am using a parameter filter for the month. The bar chart highlights the bar based on the month selected on the Parameter Filter.

       

      What I'm looking for is to create another bar chart that shows the Fiscal Year to Date totals based on the month selected.

       

      For example: If I select April, I would like the new bar chart to show October-April (FY to Date) totals for FY14-FY'17.

       

      Any help will be appreciated.

        • 1. Re: FY to Date comparison based on Parameter Month
          Joe Oppelt

          V9.3 here.

           

          I'm taking a look at this.

          • 2. Re: FY to Date comparison based on Parameter Month
            Jim Dehner

            Hi Chris

             

            I am on a different version so I will walk you through the formula

            First you need a calculated field for the actual fiscal date (the fiscal year option on the date field is for reporting and not for date math fromula)

            sf the FY date is

                 IF MONTH([Date])>=10 then MAKEDATE(YEAR([Date])+1, MONTH([Date]),DAY([Date]))

                 else MAKEDATE(YEAR([Date]), MONTH([Date]),DAY([Date])) end

             

            add a parameter for the fiscal year and month and Then create a filter for Fiscal Month     MONTH([FY YTD])<= MONTH([enter FY year and month])

             

            The result would look like this  

             

             

            Let me know if this helps

            Jim

            • 3. Re: FY to Date comparison based on Parameter Month
              Joe Oppelt

              See attached.

               

              Sheet 2 is a copy of your sheet without the filters.

               

              Sheet 3 has new stuff.  I made a copy of your parameter.  You can mix formats in a parameter.  Mine is an integer parameter with character string display.  your user sees "October", November", etc., but you have numbers to work with internally.  I made a [fiscal month] calc that takes your date values and gives you a fiscal month integer value.  (We use an October fiscal year here, so this is what we do all over the place in our workbooks.)  Now you can filter against the parameter value using the fiscal month calc.  See [limit fiscal months] that I used as a filter.

               

              Sheet 4 gives you a single YTD value.  Because the [highlight] is in the color shelf, it is breaking out April in the bars.  I would take that off the sheet.  Instead, I display the "as of" value in the title for the user.

              1 of 1 people found this helpful
              • 4. Re: FY to Date comparison based on Parameter Month
                Jim Dehner

                Sorry Chris - my first response missed the mark - needed to a different filter for the FY YTD as shown below

                 

                               MONTH([FY YTD])> MONTH([enter FY year and month]) and MONTH([FY YTD])<10 then drag the filter to the filter shelf and set to FALSE

                 

                and you will get this  (note also I sorted the FY months (manually) to go Oct - through September

                 

                 

                 

                Jim

                • 5. Re: FY to Date comparison based on Parameter Month
                  Chris Rosado

                  Thanks Joe, Sheet 4 is what I was going for. This is great!

                  • 6. Re: FY to Date comparison based on Parameter Month
                    Joe Oppelt

                    By and large you are better off handling your dates as date fields, and your date parts as numeric values rather than strings.  Just saying.

                     

                    Since you have your date field set up to start fiscal year in October, Tableau will automatically display your years in fiscal year format and in fiscal year dimensions.  If you put a filter on the sheet for YEAR([Date]), your users will see FY2014, FY2015, etc., as the filter choices, for example.

                    • 7. Re: FY to Date comparison based on Parameter Month
                      Chris Rosado

                      Is there a way to have the "Select Month" parameter filter, control the :"Select Month (copy)" parameter filter. I am hoping to limit it to just one filter on the dashboard to avoid confusion. So in other words the string parameter to also control the integer parameter?

                       

                      I understand having everything set as date fields is ideal and to keep it as integer parameters but due to other calculated fields in the actual workbook (not included in the sample workbook), I could not do it that way and had to make months as string values.

                      • 8. Re: FY to Date comparison based on Parameter Month
                        Joe Oppelt

                        Stick with your first parameter if it would be too disruptive to change over. 

                         

                        Instead, make a calc that translates the string parameter value to a number and use that calc when a number-driven calculation is more appropriate.

                         

                        CASE [Select Month]

                        WHEN "October" then 1

                        When "November" then 2

                        ...

                        When "September" then 12

                        END

                         

                        We don't have a mechanism to change parameter values dynamically.  (Not yet, anyway).  So consider driving it this way.