5 Replies Latest reply on Jul 30, 2018 2:00 AM by Shruthi B S

    Assign Number to FY Qtr's based on filter selection

    Shruthi B S

      Hi People,

       

      I have query on assigning numbers to Qtrs absed on the max Qtr selected. Workbook for reference attached nd scenario is explained below.

       

      In a single value drop down filter (FY Qtr) if FY19Q1 is selected

      , then i want to assign FY19Q1=0

                                          FY18Q4=1

                                          FY18Q3=2 and so on through a calculated field.

       

      This should be dynamic on any other qtr selection. How can i achieve this.

      I want to pick previous qtr , prev-1 qtr based on the selection of FY Qtr, hence require this.

      Thanks in advance for all the people who take time and interest in resolving the queries posted.

       

      Regards,

      Shruthi

        • 1. Re: Assign Number to FY Qtr's based on filter selection
          Ken Flerlage

          If I understand this correctly, you want to select a quarter, which will then act as the maximum quarter shown, then count each quarter backward. If that's the case, then I'd suggest that you use a parameter for the maximum quarter (I've called my parameter FY Quarter). You can then do a split on FY Qtr to create two new calculated fields, one which will give you the year number and one which will give you the quarter number. You'll need to do something similar based on the parameter to get the max year and quarter numbers. Here are those calculated fields:

           

          FY Qtr - Quarter Number

          INT( SPLIT( SPLIT( [FY Qtr], "FY", 2 ), "Q", 2 ) )

           

          FY Qtr - Year Number

          INT( SPLIT( SPLIT( [FY Qtr], "FY", 2 ), "Q", 1 ) )

           

          Max Quarter Number

          INT( SPLIT( SPLIT( [FY Quarter], "FY", 2 ), "Q", 2 ) )

           

          Max Year Number

          INT( SPLIT( SPLIT( [FY Quarter], "FY", 2 ), "Q", 1 ) )

           

          You'll also need a calculated field to filter out values greater than the max you've selected.

           

          Include

          IF [FY Qtr]> [FY Quarter] THEN

              "Exclude"

          ELSE

              "Include"

          END

           

          Drag Include to filters and select only the "Include" value.

           

          Finally, you can create a calculated field that does the math to create the counter.

           

          Quarter ID

          (([Max Year Number]-[FY Qtr - Year Number])-1)*4 + 4+([Max Quarter Number]-[FY Qtr - Quarter Number])

           

           

          See attached workbook. If this resolves your question, please be sure to mark this as the "correct answer." This will close the thread and help others who may have similar questions in the future. Thanks!

          • 2. Re: Assign Number to FY Qtr's based on filter selection
            Shruthi B S

            Hi Ken,

             

            Thanks for response. i still use 10.4 version of Tableau desktop, hence was unable to open your file. Also i see Split function is not available in 10.4 version.

            My FY Qtr list gets automatically updated, hence i will not be able to use parameter there. I can take ATTR([FY Qtr]) to get max if qtr selected.

            Could you suggest any alternative.

             

            -Shruthi

            • 3. Re: Assign Number to FY Qtr's based on filter selection
              Ken Flerlage

              Here is a version in 10.4 format. Split is definitely available in 10.4, so that should work.

               

              I'd have to think about an alternative which allows you to use a filter instead of a parameter. That makes it significantly more difficult. Since there are only 4 quarters in each year, I'd probably suggest just pre-populating the parameter before a new quarter starts--perhaps you could do this once per year.

              • 4. Re: Assign Number to FY Qtr's based on filter selection
                Ken Flerlage

                Hi Shruthi, any further questions on this?

                • 5. Re: Assign Number to FY Qtr's based on filter selection
                  Shruthi B S

                  Thanks so much. Your help is much appreciated.