3 Replies Latest reply on May 28, 2016 10:22 PM by Sonali chavan

    Restricting number of rows to show on bargraph

    Sonali chavan

      Hi All,


      I have time period hierarchy as


          -Half year



      -My scenario is to display previous 8 time period, when I am selecting 2016 year, it should display (2016,2015,2014,2013,2012,2011,2010,2009)

      This part is done.


      -Next is drill down on the year


      - When I am at year level it will give me data for all year in (i.e.. 8 bar for 8 year)

      - When I drill down to Half year level it gives me 16 bar (2 Half year for each year)

      - When I am going for qtr level it will be 32 bar similarly for month level 96 bars.


      I want to restrict this data I always want to show 5 records only.

      when I am selecting 2016 and drilling down to month level then it should only give me 8 months in 2016 not all 96 months.


      Plz help.





        • 1. Re: Restricting number of rows to show on bargraph
          vinutha vasanthu

          Hey Sonali Can you please upload a sample package for better understanding

          • 2. Re: Restricting number of rows to show on bargraph
            Andrew Watson

            Interesting challenge. I think you can do this using the Index function.


            Create a calculated field called INDEX with the formula INDEX(). That will give each bar a value, starting at 1 and out to 96 when you're at month level.


            What it sounds like you want is to return entries 94-96 at month level, 28-32 at qtr level, 12-16 at half year and 4-8 at year level - i.e. the 5 latest values based on the selection.


            Therefore we need to calculate the latest value. Create a new calculated field called LatestEntry with the formula WINDOW_MAX([INDEX]) to return the number of the latest entry.


            To finish off we need to create a calculated field to act as our filter. This would have the formula [INDEX] >= [LatestEntry] - 4. Drag that to the filters shelf and select True.


            Attached a 9.2 workbook showing it with the superstore dataset.

            1 of 1 people found this helpful
            • 3. Re: Restricting number of rows to show on bargraph
              Sonali chavan

              Hello Andrew,


              Thanks for answer. Its really helpful.


              But I have one more problem. I have one year parameter, I want to show last 5 qtr or month from current selection of year.


              1. Initially when am looking at my dashboard it should show me all year is my data.

              2. For Say, I am selecting year 2014-2015 it will give last 5 year (14-15,13-14,12-13 ,etc)

              3. Now I am drilling down at quarter level having selected year 2014-2015, then it should give me

                  4 quarter from 14-15 and last quarter from 13-14

              4. Same when I am at month level it should give me 5 months of current selected year.


              Index function works properly without my year parameter. Its also giving me correct data what I want.

              But year selection is also one of the requirement.


              Client wants to see all year at first level and then drilling down to other.


              Problem is when I am using parameter for year selection, my index is not working properly.

              and it wont give correct answer.


              Plz help....!!!


              Sry I cant share workbook, I was using trial version of tableau. and its expired now.