4 Replies Latest reply on Dec 12, 2016 7:25 PM by Nicola Prime

    Top 'N' based on a specific month with comparisons in previous/next month for same SKUs

    Nicola Prime

      Hi All,

       

      I am having an ongoing issue with a Top 'N' filter that I am hoping you can help with.

       

      I am encountering issues because I am trying to view the data based only on the current month view - i.e. in November SKUs 5,6,7,8,9 were the top 5, I want to also see the sales for October and December for SKUs 5,6,7,8,9 for comparison - it doesn't matter if these were not the top 5 in these months.

       

      I am trying to present the data in column format as per below and I want it to be dynamic - i.e. when I change the current month to December, I want all this to automatically update.

       

       

      Currently I am encountering the following problem: Tableau is showing the top 5 based on Dec'15 + Oct'16 + Nov'16 + Dec'16 and I can't figure out a way to get it to order the top 5 based only on the selected month (Nov'15).

       

      Using the Superstore dummy data, it looks as per below - you can see it is basing the top 'n' on Oct+Nov+Dec.

       

       

      Thanks in advance!

       

      Nicola

        • 1. Re: Top 'N' based on a specific month with comparisons in previous/next month for same SKUs
          Chris Dickson

          Hi Nicola,

           

          what version of tableau do you need the workbook in - I have built the attached in version 10.

           

          Please let me know if you need help achieving this step by step.

          • 2. Re: Top 'N' based on a specific month with comparisons in previous/next month for same SKUs
            Nicola Prime

            Hi Chris,

             

            Thanks for this - A few questions came up when I was trying to replicate this:

                 1) The "last 3 months" formula - I want to capture just one month before and one month after the "Order month", and tried to add this to the formula (middle portion), however, it did not work so I must be interpreting it incorrectly:

                           "datetrunc('month',[Order Date])>=(dateadd('month',-1,datetrunc('month',[Orders Month])))

                           and

                           datetrunc('month',[Order Date])>=(dateadd('month',1,datetrunc('month',[Orders Month])))

                           and

                           datetrunc('month',[Order Date])<=datetrunc('month',[Orders Month])"

             

            I actually had a formula which works on my own data but it seems that it doesn't work on the dummy data. I am trying to say, "take Dec'14 data, take the order month -1, order month +1, and the order month". Any idea why this does not work?

                      "IF

                        DATEDIFF('month', [Order Date], #2014-12-01#) = 0 OR

                        DATEDIFF('month', [Order Date], [Order Month]) = -1 OR

                        DATEDIFF('month', [Order Date], [Order Month]) = 1 OR

                        DATEDIFF('month', [Order Date], [Order Month]) =0

                      THEN "Show" ELSE "Hide" END"

             

                 2) What exactly is the "In the month" formula doing?

                           "if datetrunc('month',[Orders Month])=datetrunc('month',[Order Date]) then [Sales] end"

            I can see that if I change the Product Name filter to be based on "Sales" rather than "In month sales", the data changes. I interpret the formula as saying, "if the month of the order month = the month of the order date, then show the sales" - looking at how the data changes between the two, it looks like it is focusing the Top N sort on the month, but I can't see this from the formula?

             

                 3) What if I want to add Category or Segment into the mix? I was using an INDEX() Advanced in order to get the Top N by Category - I used the "Use Advanced Options in the Calculation (Alternative Example)" (http://kb.tableau.com/articles/knowledgebase/finding-top-n-within-category)

             

            Thanks a lot,

             

            Nicola

            • 3. Re: Top 'N' based on a specific month with comparisons in previous/next month for same SKUs
              Chris Dickson

              Hi Nicola,

               

              it would probably be easier to sit down over the phone for this

               

              1. you formula will always return false because a date cannot both be more than 1 month in the future and less than this month, i have included the correct calculation in the attached workbook.

               

              2.this formula is used to isolate the single months sales for the purpose of sorting and then filtering, it means the 1 month either side is ignored when it comes to calculating the index

               

              3. This depends on how complicated you want to get, in the attached workbook i have shown you how to switch between dimensions using a parameter, this works fine if you want to look at sub category or product or segment, but if you want top n products per sub category there is a different mechanism we need to use as the basis of the filter is 'table down' in this example therefore if we brought in products and sub category you'd get the top 5 products from the first category and not below. To do this we would create a second sheet and depending on the parameter selection we would 'show' or 'hide' the relevant sheets.

               

              Hope this helps.

              • 4. Re: Top 'N' based on a specific month with comparisons in previous/next month for same SKUs
                Nicola Prime

                Hi Chris,

                 

                Sorry for the delayed response, something else came up that needed my attention.

                 

                This is great! Thanks a lot for this - finally I can I present my data in the way I need!

                 

                Thanks

                 

                Nicola