6 Replies Latest reply on Aug 13, 2013 7:55 AM by Jonathan Drummey

    Top 10 Bar Graph

    Buddy DILELLA

      I am trying to get the Top 10 PPA in a Bar graph during a 4 week period


      I use the index() in a calculated function but it doesn't display all of the apps during a 4 week period that  I am looking for.  I adjust the Rank parameter but I seem to getting over 10 apps displayed during a 4 week period.


      All I want is a bar graph with the count for the TOP 10 Apps each displaying a count for a 4 week period.


      A test workbook is included.


      Thanks for the help....

        • 1. Re: Top 10 Bar Graph
          Pedro Machado



          How are you sorting the PPAs to get the top 10?  By Number of Records? Sum(TCK)? CountD(TCK) or something else?


          I am not sure I understand your question, but it sounds like you want to first identify the top 10 PPA and then show the trend of CountD(TCK) for the last 4 weeks.


          If so, here is what I would do:


          1) Create a computed set that identifies the Top 10 PPA based on whatever field you need.

          See http://onlinehelp.tableausoftware.com/v8.0/pro/online/en-us/help.htm#sortgroup_sets_create.html

          2) Filter based on that set

          3) Build your visualization


          You may have to do this TOP N selection by formula instead of using one of the aggregations if you need to consider only the last 4 weeks  worth of data as opposed to the whole data set. Your formula would need a condition to only include data in the last 4 weeks.


          I gave this a try, but I wasn't sure how you want to define last 4 weeks (the tableau relative date way or date > today() - 4 * 7 days).


          There may be another way to do this using the condition tab, but this way should work.


          Let me know if I misunderstood what you are trying to do.



          • 2. Re: Top 10 Bar Graph
            Buddy DILELLA

            First I am sorting by PPA /Number of Records descending order.


            Then I do a count on TCK…

            • 3. Re: Top 10 Bar Graph
              Buddy DILELLA

              I looked at your workbook and I can't believe this is the only way this can be done. 


              I am wondering if anyone else has any ideas on how to post the TOP 10 PPA's over a 4 week period using a bar chart?


              Pedro,  I thank you for your time and effort. It's very much appreciated.

              • 4. Re: Top 10 Bar Graph
                Jonathan Drummey

                I'm not sure I'm understanding your response, is there a way that this solution does not meet your needs? The method that Pedro described would be the highest performing, because the top 10 calc in the Set will be done inside the data source. You could probably do some sort of top 10 index calc, but that would be slower because it requires a lot more data to be returned from the data source to perform the table calculation.


                One change to make to the view is to change the WEEK(Date) to be a continuous (green) pill, that way you'll see every week for every ppa.



                • 5. Re: Top 10 Bar Graph
                  Buddy DILELLA



                  Nothing is wrong with Pedro’s suggestion I just have to get my head around “sets” which I have never ventured into before.


                  I was just wondering if there is a way I can do it with the “index” technique?   I did the reports originally using the index and rank the top 10 PPA’s using the type of bar chart Pedro used in the TOP PPA ALL WEEKS  .  When I changed it you use bar charts over a 4 week period using index, I couldn’t get it to work correctly.


                  Looking at Pedro’s workbook, I notice some of the ppa’s display a 1 week period, 3 week period, and 4 week periods.  I know there is data there to consistently display 4 weeks of data (4 bars) for each PPA.  So there is an issue there.


                  Thanks for the feedback from you and Pedro.  It’s greatly appreciated.

                  • 6. Re: Re: Top 10 Bar Graph
                    Jonathan Drummey

                    I'm not clear on what you mean by "consistently display 4 weeks of data". There are not 4 continuous weeks of data for each and every PPA, see the "data is sparse" worksheet in the attached (there are a lot of empty spaces). If you want to be showing the most recent 4 weeks of data for each PPA, then you'd need to either do more work in the SQL to add a new column to identify the week or a complicated set of table calculations.


                    I set up the Index option in the "workout" and "bar chart" worksheets, the key is for the Rank calc an Advanced Compute Using on Week of Date, ppa (in that order) with a sort on countd/TCK/descending.