10 Replies Latest reply on Nov 4, 2013 1:36 PM by Katherine McGraw

    Difference from last year Column Percent

    Teresa Obis

      I am new in Tableau and I have a problem that I can not solve.

      Please can anyone help me?

       

      I have my Sales by Brand (rows) and Year(columns).

      I've got

      1) the Sales Difference from last year in % for each brand:

      (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) /ABS(LOOKUP(ZN(SUM([Sales])), -1))

      2) the market share of each Brand for each year (as my brands are in rows, is the Percent of Total Down Pane):

      SUM([Sales]) / TOTAL(SUM([Sales]))

       

      Now I would like to compute the Difference from the market share from one year to another (just substrat one from another). I would like to perform a secondary calculation of a results, but I haven't this option for the Percent of Total.

       

      I have tried with:

      ZN(SUM([Sales]) / TOTAL(SUM([Sales]))) - LOOKUP(ZN(SUM([Sales]) / TOTAL(SUM([Sales]))), -1)

      but I do not get the expected results.

       

      I have looking for at Manual, Knowledge base and Forum but I could not find any trick.

       

      Find attached an Example.

       

      Any help would be appreciated.

       

      Teresa

        • 2. Re: Difference from last year Column Percent
          Teresa Obis

          Oh, my God! I am going to be crazy.

          I try to replicate your answer, but I could not all day long.  I was looking into every calculation to see what are the difference.

          At last I've seen it.

           

          I send you two packeges workbook. In your file "percent diff in market share" all work fine. I prefer to have the Difference of Market Share just as a subtraction because Market Share is computing as a Brand Percent. Following your example the Difference YoY Share was -2,5%, 0% and +2,5%, as I would like to have.

           

          But in my example I have 20%, 23% and 33%. All the computation has exactly the same formula, I promise. The only thing that it is different is that in my example the last item (Diff YoY Share) has a mark in "Computing using ..." Date (my Year's variable), and you do not have any mark at this.

           

          Now my question is easy, how can I uncheck Date in the Computing using menu?

           

          Thank you very much for your fast and very understanding answer. The video and the file have help me a lot.

           

          Teresa

          • 3. Re: Difference from last year Column Percent
            Joe Mako

            You did not set the partitioning correctly. If you watch the video again, at the 2:00 mark, I go through the partitioning you want set. You will want to set it the same way as in the video for your new field "Diff YoY Share".

             

            From the "example.twbs" you just attached:

             

            1. Bring up the context menu for the pill "Diff YoY Share", and select  "Edit Table Calculation"

            2. Change the drop-down that is at the top of the dialog, called "Calculated Field", changing it from "Diff YoY Share" to "Market Share"

            3. Change the drop-down for "Compute using" from "Date" to "Brand"

             

            What is going on is you need to set the partitioning differently for each table calc, and this dialog allows you to do that. Because you want "Market Share" to compute using "Brand", and the "Diff YoY Share" to compute using "Date".

             

            Is the pace of the video too fast, or how do you think I could make it clearer?

            • 4. Re: Difference from last year Column Percent
              Teresa Obis

              I did all you say. But I do not when, I have mark "Diff YoY Share" with  "Compute usaing ..." (right click over the variable name) as a Date, and then even I go to the Edit the Calculate Field and say "Diff YoY Share" has to usign Date it doesn't work. 

               

              In your example you don't have this mark and that is the only difference I have found. Please try to modify my "Diff YoY Share" example because I could not.  Even I redo your steps in the Example File, I can not get the right results. And I do not know what I am doing wrong.

               

              thanks,

               

              Teresa

              • 5. Re: Difference from last year Column Percent
                Joe Mako

                In the "Edit Table Calculation" dialog box, are you changing the drop-down called "Calculated Field", changing it from "Diff YoY Share" to "Market Share"?

                 

                When "Market Share" is the selected "Calculated Field" in the dialog box, you want to set the "Compute using" drop-down in the dialog from "Date" to "Brand"

                 

                When you have set the partitioning differently as I have instructed here, and in the video, you will no longer see the dot/mark next to "Date" when you are only looking at the context-menu option for compute using. When you set the compute using from the context menu, adding the dot, you are setting the partitioning the same for each calculated field. In order to get the result you want, you need the partitioning set different for each calculated field. Each level of aggregation gets its own partitioning.

                • 6. Re: Difference from last year Column Percent
                  Teresa Obis

                  Thank you very much for your kind answer. Now I understand my problem.

                   

                  I set the compute using from the context menu, adding the dot, so I am  setting the partitioning the same for each calculated field.

                   

                  Is there any way to remove the dot in the compute using context menú?

                   

                  Teresa

                  • 7. Re: Difference from last year Column Percent
                    Joe Mako

                    > "Is there any way to remove the dot in the compute using context menú?"

                     

                    No, you must use the dialog box, because the dot means all calculated fields within this pill will use the same partitioning. And the dot is removed when the pill contains calculated fields that are set to different partitioning. You can only set different partitioning to the calculated fields within the pill by using the dialog box.

                    • 8. Re: Difference from last year Column Percent
                      Teresa Obis

                      Great!!!

                       

                      It works.

                       

                      Thank you very much.

                       

                      Teresa

                      • 9. Re: Difference from last year Column Percent
                        Cathy Bridges

                        Hi Joe -

                        I also need difference in market share year-over-year. It seems that my table calculations are structured like your examples, but the math isn't working. What am I missing?

                        Thanks,

                        Cathy

                        • 10. Re: Difference from last year Column Percent
                          Katherine McGraw

                          Joe,

                          Wonderful explanation. Thank you!