5 Replies Latest reply on Feb 23, 2017 1:13 AM by Simon Runc

    Match Dynamic Top N results for Current Year and YOY Calculations

    Kaela Blenkush

      Apologies if this has been asked before - I searched through the forums pretty extensively but haven't found a solution yet.

       

      Using Superstore data as a stand-in, my goal is to be able to have a dynamic top N number of product sub-categories that match results for both a point-in-time/current year as well as year-over-year calculation. I've figured out dynamic top N and the year-over-year calculations; the issue is that the top 5 (for example) that are included in the point-in-time pie chart don't always match the top 5 that are included in the year-over-year calculations. There are cases where product categories may have orders in December 2014, but not December 2015.

       

      For example, in the packaged workbook I've attached, if you select December 2015 and adjust the number of sub-categories to display to 13, copiers will show up in the pie chart as part of the top 13, but not in the year-over-year calculations. It's hard to replicate with this stand-in data, but in the project I'm working on there are cases where copiers would not show up in the pie (because they have 0 quantity for 2015) but do show up in the YOY calculations (because they were in the top 5 in 2014).

       

      Is there a way to get the top N group that is returned to only show the top N for a current selected year, and have that same group of sub-categories match in the YOY calculations?

       

      Some notes on what I've tried so far:

      • Using a date quick filter doesn't work on the YOY calculations because then it excludes the required "previous year" component
      • I tried using a date component with the index() calculation ("Rank" in the workbook) with the idea that it would only return top N for the most recent year, but couldn't get this to work
        • for example: IF ATTR([Order Date]) = [Select Date Parameter] THEN INDEX() END
        • also tried: IF MAX([Order Date]) = [Select Date Parameter] THEN INDEX() END

       

      If anyone has a solution for this, I would be forever in your debt.

      (Using Tableau 10.0)

        • 1. Re: Match Dynamic Top N results for Current Year and YOY Calculations
          Simon Runc

          hi Kaela,

           

          Yes I think this is possible (...famous last words!), but can I just check my understanding...

           

          The List of Top N SubCategories is based on the Top N for the year, for the selected date. So, if I select January 2014, the Top N is the Top N for 2014 (full Year, or YtD for incomplete year)?

           

          Once we have this Top N list, we want this to be "held" in the Year on Year (so same Top N as for FY 2014), which shows the 2014 vs 2013 variance. So the YoY comparison is also dynamic to the selected year (January 2014 in our example)

           

          Can I also ask if you are open to the idea of parameters? The advantage being that it makes pulling a single Top N easier, and the disadvantage being that the list of values in the parameter need 'manual' updating (eg. If the parameter for data contained the, selectable values, Jan 2013, Feb 2013...Feb 2015, if new data was added and we now have data for March 2015, we'd need to add that to the parameter list of values, or hold all values into the future and "hope" the user doesn't select a date into the future! Also parameters are only single select)

          • 2. Re: Match Dynamic Top N results for Current Year and YOY Calculations
            Kaela Blenkush

            Hi Simon,

             

            To clarify, the top N as it is set up will select the top N for the month and year selected. So selecting January 2014 will show the top N based on quantity for the month of January 2014.

             

            Yes, correct – I’d like to have that top N list “held” in the year-on-year calculations.

             

            I am certainly open to parameters! Right now I’m using one parameter to control the Top N (Select number of sub-categories to display) and one to control the date calculation.

             

            Thanks!

            • 3. Re: Match Dynamic Top N results for Current Year and YOY Calculations
              Simon Runc

              hi Kaela,

               

              Excellent...

               

              So (I think) the easiest way to do this is to use sets...sets are computed before any regular-dimension filters are added, so the members of the set are the same regardless of the filtering (context filters, btw, bump the filter up the ordering, so do affect the set, so in your example the filter on state does affect the subcats in the set).

               

              So first I created this field, based on the parameter date filter selection

              [Sales - Current Year/Month]

              IIF(DATETRUNC('month',[Order Date]) = [Select Order Date],[Sales],NULL)

               

              and then set up the set, from the SubCategory like this

               

              I've then just used this set in the filter.

               

              btw I've not amended your "Current Year Share - Table" tab (...but with the set it's very easy...let me know if you need a hand...but in Short (IIF([Sub Cats - Current Month/Year],"Top N", "Others") will do the job)

               

              Hope that helps, and makes sense, but let me know if not

              • 4. Re: Match Dynamic Top N results for Current Year and YOY Calculations
                Kaela Blenkush

                Hi Simon,

                 

                This looks really good – I think it gets me about 90% of the way there. The only issue is I still need the other sub-categories that aren’t in the top N to roll up into one “all other” bucket included in the chart.  So really it would be Top 5 + a sixth slice with the window sum of the remaining categories.

                 

                I tried modifying the “In Top N” and “Others” fields to be based on the set you created instead of the index table calculation, but that doesn’t seem to work.

                 

                I think what I will try to do is add a column in the data source that has a static year-over-year change and use that instead of trying to use table calculations. I’ll let you know if that works.

                 

                Again, thank you so much for your help!

                Kaela

                • 5. Re: Match Dynamic Top N results for Current Year and YOY Calculations
                  Simon Runc

                  hi Kaela,

                   

                  Glad it helped...

                   

                  So we can create the same fields as you did with index()...

                   

                  [Sub-Category Grouping]

                  IF [Sub Cats - Current Month/Year] then

                  "Top " + STR([Select Number of Sub-Categories to Display])

                  else "All Others" end

                   

                  to go along with

                  [Sub-Category (Edit)]

                  IF [Sub Cats - Current Month/Year] then

                  [Sub-Category]

                  else "All Others" end

                   

                  and then everything is as it was before.

                   

                  On the YoY...I'm not 100% sure why you are calculating this way, and why those fields come in from a blended data source, but don't know your final version!! Even if you only have [Sub-Category (Edit)] in the VizLoD, you can still blend on Category (or just create a [Sub-Category (Edit)] in the secondary and blend on that...as parameters are global it will change as the one on the primary.  Either way, I don't think it should be any different from your set up before (just using sets rather than an index() to get the Top N). I've done a simple version of YoY in the attached to show you what I mean

                   

                  A YtD Flag is useful to have in the data_view, but could also be calculated in Tableau using an LoD