4 Replies Latest reply on Mar 26, 2014 4:59 AM by Teresa Obis

    Top N plus closer N

    Teresa Obis
    Teresa Obis Mar 23, 2014 1:38 PM

      Hi guys,

       

      I still have problems with the list of my closer products in the ranking.

      Now a day I have done a list that show the 5 leaders, if my product is in this list (left image), but only the to 2 leaders and the two products close to the mine and my product when my product is not in the 5 leaders (middle image)

      But I do not what happend when my product is just the 5th, I've got a null value. Why?(rigtht image).

       

       

      Also I have problems to get the index position and the row total at the same time (left) and to make changes in the ranking when I select a month

       

      I would like to introduce the total sales, even I use the Index variable and I would like to make an action to get the raking by month.

       

      Is that possible?

       

      Find attached the workbook with the example

      • 76 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Top N plus closer N
          Noah Salvaterra
          Noah Salvaterra Mar 23, 2014 2:42 PM (in response to Teresa Obis)

          I found the selected products calculation pretty confusing, but after staring at it a few minutes I realized it was because of the logical testing that was happening in the then portion of the if-then statement. I can't think of a good reason why this would be wrong, but it is unusual. It seems like it may not pick up table calc defaults when used in this way. If you go into edit table calc for selected products, there is no option to change the partitioning for the index, so that would seem to confirm this hypothesis.

           

          I tried rewriting this calculation by replacing each then with an and adding parenthesis as needed. Here is the result:

           

          IF ISNULL(WINDOW_sum([Select product])) and INDEX()<=5 then True

          ELSEIF WINDOW_MAX([Select product])<=4 and INDEX()<=5 then True

          ELSEIF WINDOW_MAX([Select product])=5 and (INDEX()<=2 OR

              LOOKUP(ATTR([Product]),-1) =[My Product] OR

              LOOKUP(ATTR([Product]),0) =[My Product]  OR

              LOOKUP(ATTR([Product]),1) =[My Product]) then True

          ELSEIF  (INDEX()<=2 OR

              LOOKUP(ATTR([Product]),-1) =[My Product] OR

              LOOKUP(ATTR([Product]),0) =[My Product]  OR

              LOOKUP(ATTR([Product]),1) =[My Product] ) then True

          ELSE False

          END

           

          It seems to work the way you want now, no? I attached your workbook with this change.

          1 of 1 people found this helpful
          • 2. Re: Top N plus closer N
            Teresa Obis
            Teresa Obis Mar 23, 2014 3:44 PM (in response to Noah Salvaterra)

            Yes Noah, your answer is very useful and creative. We do not know why Tableau doesn´t let us to change the partitioning the the index(), but with the last ELSE I can solve my problem.

             

            Do you know why I can not get the Totals when I put the index in the table.

             

            Thak you very much.

             

            Teresa

            • 3. Re: Re: Top N plus closer N
              Noah Salvaterra
              Noah Salvaterra Mar 23, 2014 4:22 PM (in response to Teresa Obis)

              I think the issue is that the index is being computed using the total sales, as a discrete measure it is fixed to a particular row, but that may no longer be the correct row when you slice by month. I added a sheet to the workbook below that shows a way to index each month as well as the grand total.

               

              I'm kind of curious what you're doing here... can you give me an idea what the use case is for this?

              1 of 1 people found this helpful
              • 4. Re: Top N plus closer N
                Teresa Obis
                Teresa Obis Mar 26, 2014 4:59 AM (in response to Noah Salvaterra)

                Hi Noah,

                 

                Thank you, your result is very close the one I'd like.

                Now I would like to be able to see the Product order by Index() by Grand Total if I have no month selected, but if you select a month, Can I show the files by the order of Sales in that month? Could you help me to do it, if it is possible.

                 

                My goal is to show the ranking of products by Sales (either at the Total Sales or at one specific month Sale). With this my customer (the product manufacturer) could see if his/her product has improve its possition over the time. For example, the total sales says that P8 was the 3rd, but last month it looses that possition by P2.  Or P8 is in 3rd position thanks to the sales obtained in February when it was the 2on sale product.

                 

                Thank you very much,

                 

                Teresa