3 Replies Latest reply on Jun 23, 2016 8:10 AM by Vincent Baumel

    Listing Top N based on the result of a Table Calculation

    Syed Zafar Naqvi

      I am attaching a workbook based on the super store sample. I am trying to do the following:

       

      1. Charting sales over a given time period (sliding window) - worksheet "SalesOverTime"

      2. Identifying sub categories that have had a 100% increase in sales during the same time period. - worksheet "Increase > 100%"

      3. List Top 5 subcategories based on the % increase in sales during the same time period (a list or a wordcloud maybe). - worksheet "Top5"

      4. Bring the above together in a dashboard.

       

      The key requirement is that the time window needs to stay customizable, applicable to all 3 sheets and the dashboard needs to display results accordingly.

       

      The first one is pretty simple and I have displayed a moving average there , for the second part i have created a table calculation as follows:

       

      "variable name PercentIncreaseInTimewindow" formula:

      (Lookup(SUM(Sales),LAST()) - lookup(SUM(Sales), FIRST()))/lookup(SUM(Sales), FIRST())

       

      While for the third, i was able to create a line chart showing top 5 sub categories. I want this to just be list of names OR a word cloud - since its based on a table calculation, as soon as i remove the date dimension from the columns, i loose the results.

       

      Would appreciate any help!

        • 1. Re: Listing Top N based on the result of a Table Calculation
          Vincent Baumel

          The first thing I did was duplicate Top5 as a crosstab. Then I dragged PercentInceaseInTimeWindow off of the Measure Values card, right clicked the Month of Order Date and deselected Show Header. Then I hit ctrl+w to swap columns and rows (still one of my favorite Tableau shortcuts!), then ctrl+dragged both Top5Flag and Product Sub-Category from the Rows shelf to the Text button in the Marks card. From here it was just a little formatting; in the dashboard I brought the crosstab out and placed it under the graphs. Then I widened the Product Sub-Category field all the way out to the right and voila! I think it's what you're going for.

           

          xtab.PNG

          dashboard.PNG

           

          The list is dynamic, and the slide filter adjusts everything. Let me know if you can get it working!

          1 of 1 people found this helpful
          • 2. Re: Listing Top N based on the result of a Table Calculation
            Syed Zafar Naqvi

            Thank for replying Vincent, that's great help! So I followed the steps and this is what I get:

             

            PercentGrowthInGivenTimeWindow.png

             

            And I am guessing that I cannot do way with the Months from the view? I just have to hide them by expanding the sub category label across the whole width. Right?

             

            And If that is the case then this cannot be further modified to just a word cloud of the top 5 sub category names...

            • 3. Re: Listing Top N based on the result of a Table Calculation
              Vincent Baumel

              Yes, you're right. Taking the Months out of the view breaks the Top5Flag table calculation, so it must remain. Expanding the sub category field across the whole width is how you "hide" the fields to the right. This is creating a dynamic list of the top 5 sub category names, but if you wanted to do them as a word cloud we'd need to redesign the whole worksheet - there's not an easy way (that I can think of) to switch from this to a word cloud. If my initial response helped you out please mark it as helpful so that others can find it more easily. Thanks for the positive response!