5 Replies Latest reply on Jul 2, 2014 2:13 AM by Amandine Ricaud

    Top N Products relative to previous month

    Amandine Ricaud

      Dear all,

       

      I would like to ask for your help to compile a visualization which I am currently struggling with.

       

      What I am looking for is to display a Top N product for a given month, showing next to the data what is the evolution compared to the previous month with an arrow and how much is the percentage difference.

       

      So far I only managed to have the list of products for my given month and the arrow showing the evolution from the previous month.

      But the sorting by descending order does not seem to be correct, hence a Top N does not work, and I do not know how to display the percentage difference for the previous month.

       

      That's a tricky one, at least for me :-)

       

      Attached you can find a sample workbook with dummy data taken from the Superstore Sales data source.

       

      Any suggestion would be very helpful.

       

      Many thanks in advance,

      Amandine

        • 1. Re: Top N Products relative to previous month
          Michel Caissie

          Amandine,

           

          Here is a possible solution.

           

          -I make use of multiple axis. Each axis have its own set of shelves so you can set properties on your measures independently. (i am glad to see that now in 8.2 when setting a dual-axis , it no more brings the Measure Names on the color shelf of all axis)

           

          -I bring both month in the view (needed for computation)  but using the last() function I filter to show only the latest.

           

          -To Sort based on the current month sales, i bring this value on the rows shelf (unselect Show Header)  but i  return either the positive or the negative value depending on the Sort Order parameter.

           

          -Finally, I use the index() and last() function  along with the Top N parameter  to keep only the Top N values in the view.

           

          Michel

          • 2. Re: Top N Products relative to previous month
            Amandine Ricaud

            Dear Michel,

             

            thanks a lot for your help. It's exactly what I was looking for :-)

             

            It just seems that there is a problem with the Top N.

             

            If you remove the Top N filter from the visualization you can see that the sub-category "Binders and Binder Accessories" is at the rank 13 with $6409 in May. But when you add the Filter Top N, the group "Binders and Binder Accessories" always appears at the last position when the Top N is between 7 and 13. So it looks that the Top N is not 100% correct.

            Do you know where this issue might be coming from?

             

            Thanks again for your help.

             

            Kind regards,

            Amandine

            • 3. Re: Top N Products relative to previous month
              Michel Caissie

              You are right Amandine, the Sorting does not work. What i see is because both month are in the Details, the index functions (last,first,index) are computed based on the Sum of the Sales for both month. And since binders and Binders had 40k in April the are always in the Top 7.

               

              I haven`t found a rapid solution, but i don`t have much time to investigate for now. I`ll check later.

              Maybe someone else will jump in and find a solution to this interesting problem.

               

              Michel

              • 4. Re: Top N Products relative to previous month
                Michel Caissie

                Amandine,

                 

                Check in the attached,  the sorting should be ok now. I think i overcomplicated things in my first try.

                 

                Also i made two filters. One will keep the Top sales and Sort them  asc or desc,    and the second will give you the Top sales when selecting the desc order ,  and the Bottom sales when selecting the asc order.

                 

                Michel

                • 5. Re: Top N Products relative to previous month
                  Amandine Ricaud

                  Dear Michel,

                   

                  thank you very much for having looked into it one more time.

                  It works perfectly fine now. That's great.

                   

                  Many thanks for your help,

                  Amandine