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

    Restricting number of rows to show on bargraph

    Sonali chavan
    Sonali chavan May 25, 2016 9:50 PM

      Hi All,

       

      I have time period hierarchy as

      -Year

          -Half year

            -Qtr

             -Month

      -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.

       

       

      Regard,

      Sonali.

        • 1. Re: Restricting number of rows to show on bargraph
          vinutha vasanthu
          vinutha vasanthu May 26, 2016 7:05 AM (in response to Sonali chavan)

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

          • 2. Re: Restricting number of rows to show on bargraph
            Andrew Watson
            Andrew Watson May 26, 2016 7:06 AM (in response to Sonali chavan)

            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
              Sonali chavan May 28, 2016 10:22 PM (in response to Andrew Watson)

              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.