10 Replies Latest reply on Feb 22, 2016 11:50 AM by spenser.troiano

    YoY absolute difference in market share %

    spenser.troiano
    spenser.troiano Feb 11, 2016 3:38 PM

      Hi all,

       

      I'm trying to create a table for end users that shows the absolute difference in market share percentage year over year for any two selected years. Fore example, see the tables below showing market share of product categories by region by any selected year:

       

      Each table shows the market share of each product category by region for the indicated year (Year on Detail mark). In 2011, Furniture products comprised 22.6% of the Central region market. In 2012, Furniture products comprised 20.4% of the Central region market. I would like to generate a new table that shows the absolute difference in market share% between years. For example, Between 2012 and 2011, Furniture products reduced from 22.6% of the market to 20.4% of the market, so my new table should display the sum of 20.4 - 22.6, or -2.2 (mock up solution below). Ideally the solution would be flexible for any two selected years:

       

       

      Any thoughts? I feel like there is a pretty simple solution that's just evading me. Thanks!

      • 555 Views
      • Tags:
        • 1. Re: YoY absolute difference in market share %
          Shinichiro Murakami
          Shinichiro Murakami Ambassador Feb 11, 2016 4:04 PM (in response to spenser.troiano)

          Change the table calculation into LOD calc.    // Overview: Level of Detail Expressions

          Then add another table calc.

           

          [Regional Market Share (copy)]

          {fixed [Region],[Category],[Year(OD)]:SUM([Number of Records])} /

          {fixed [Region],[Year(OD)]:SUM([Number of Records])}

           

           

          9.2 attached.

           

           

          Thanks

          Shin

          1 of 1 people found this helpful
          • 2. Re: YoY absolute difference in market share %
            spenser.troiano
            spenser.troiano Feb 11, 2016 5:23 PM (in response to Shinichiro Murakami)

            Thanks Shin- This is very close. Ideally I'd like to only show the difference in market share table for the Max year, and hide everything else. In other words, I only want to display this table:

            I put the first table calc into the detail mark which hid it, but I'm also trying to remove the empty 2011 table (see attached). Any thoughts??

            1 of 1 people found this helpful
            • 3. Re: YoY absolute difference in market share %
              Shinichiro Murakami
              Shinichiro Murakami Ambassador Feb 11, 2016 10:02 PM (in response to spenser.troiano)

              You can duplicate worksheet.

              1st sheet = 2011, 2012.

              2nd sheet shows blank 2011 and 2012 as delta .

              You can "hide" (not exclude) 2011 from table, then only shows 2012 as delta.

              You create dashboard to show two worksheet vertical.

               

              Thanks,

              Shin

              1 of 1 people found this helpful
              • 4. Re: YoY absolute difference in market share %
                Shinichiro Murakami
                Shinichiro Murakami Ambassador Feb 12, 2016 5:18 AM (in response to Shinichiro Murakami)

                Enjoy Illustration tutorial

                 

                 

                 

                 

                 

                 

                 

                 

                Final View on Dashboard

                Thanks,

                Shin

                • 5. Re: YoY absolute difference in market share %
                  pooja.gandhi
                  pooja.gandhi Feb 12, 2016 5:23 AM (in response to spenser.troiano)

                  Spenser,

                   

                  Just use the Lookup Max Year calc in filter instead of the original date dimension and select 2012. Your table calcs should be retained and 2011 wouldn't be visible.

                   

                  1 of 1 people found this helpful
                  • 6. Re: YoY absolute difference in market share %
                    spenser.troiano
                    spenser.troiano Feb 17, 2016 12:33 PM (in response to pooja.gandhi)

                    Pooja and Shin,

                     

                    Thanks again for your sage advice. Now that I've created the market share delta calculation I have two follow-up questions I'm wondering if you can consult on:

                     

                    1. Is there a way to a add column grand total to aggregate market share delta across all regions. For instance, between years 2011 and 2012 Technology products grew from 23.4% of the total market in 2011 to 25% of the market in 2012, a difference of roughly 1.6. However when I add a column total to this table (See first sheet) I am restricted to aggregating based on the Region level of detail results. Ideally I'd like to total at a higher level of detail.

                     

                    2. I'd like to be able to rank each product category based on the calc for each region. For example, in the table above comparing 2012 to 2011,  the largest gainer in each region were:

                     

                    Central - Technology (4.9)

                    East - Technology (0.1)

                    South - Technology (2.9)

                    West - Furniture (0.3)

                     

                    I'd like to create a table that will display the name of the product category with the largest gain in the regional market share table calculation, but I'm having trouble getting the rank function to work properly with the table calc (See calculated field "Rank Market Share Delta" in attached, on "Rank" sheet)

                     

                    I can't thank you both enough for your help!

                    • 7. Re: YoY absolute difference in market share %
                      Shinichiro Murakami
                      Shinichiro Murakami Ambassador Feb 17, 2016 4:36 PM (in response to spenser.troiano)

                      1. I could not find easy way to combine detail and total. Tried dashboard but the "measure filter " ca nnot be effective across multiple sheet.

                      instead, I prepared parameter button

                       

                      2. Prepared couple of additional calculated Fields. and used parameter in this case which I think much reasonable.

                       

                      [Delta % YoY (current Year)]

                      ({fixed [Region],[Category]: sum( if [Year]=[Year(OD)] then [Number of Records] END)}

                      /{fixed [Region]: sum( if [Year]=[Year(OD)] then [Number of Records] END)})

                       

                      [Delta % YoY (Previous year)]

                      ({fixed [Region],[Category]: sum( if [Year]-1=[Year(OD)] then [Number of Records] END)}

                      /{fixed [Region]: sum( if [Year]-1=[Year(OD)] then [Number of Records] END)})

                       

                      [Delta % YoY]

                      [Delta % YoY (current Year)]-[Delta % YoY (Previous year)]

                       

                      [Biggest Gainer]

                      if {fixed [Region]:max([Delta % YoY])} = [Delta % YoY] then [Category] END

                       

                      In case, you need to compare 2012 vs 2010 for example, you need to add one more parameter of

                      <From> and <To>

                       

                      Thanks,

                      Shin

                      • 8. Re: YoY absolute difference in market share %
                        spenser.troiano
                        spenser.troiano Feb 22, 2016 8:44 AM (in response to spenser.troiano)

                        Hi,

                         

                        Coming back to this- I'm having trouble getting the difference calculation respond appropriately to filters. For example, if I want to show market share for jut the Corporate and Small Business customer segments, I get the following market shares for tears 2011 and 2012:

                        2012 Market Share:

                        2011 Market Share:

                        Difference in Market Share Calc (between 2011 and 2012):

                         

                        In the above example, Furniture category in the central region dropped from 20.88% market share in 2011 to 19.25% in 2012 among Corporate and Small Business customers. I would expect our difference calc to show -1.6 to reflect this (20.88 - 19.25). Instead the difference calc shows -2.2 which is the difference if there were no filter applied. In the above example the filter doesn't change the calc even though it is applied in the sheet. Does this have something to do with building the difference calculation with level of detail functions? The actual sheet I'm working on has about 14 filters that users can apply. Any thoughts on how to allow filters to work with this calculation? Including

                        Shinichiro Murakami

                        Pooja Gandhi who have helped on this.

                         

                        Thanks!!

                        • 9. Re: YoY absolute difference in market share %
                          pooja.gandhi
                          pooja.gandhi Feb 22, 2016 8:54 AM (in response to spenser.troiano)

                          Hi Spenser!

                           

                          So this is about order of filter execution in Tableau. You are using FIXED expressions and hence Tableau will ignore the dimension filters because dimension filters are applied after FIXED level of detail calcs. So for this 2 work, you are going to want to add the 2 dimensions that you did not use in the dimension declaration of your FIXED calc. Meaning clicking on the dropdown of customer segment and ship mode that you have in filters and selecting 'add to context'. The pills should turn gray once you do that. Context filters are executed before FIXED and hence the calc would respect the filters:

                           

                           

                          Heres a great resource that talks more about this topic:

                           

                          Filters and Level of Detail Expressions

                          1 of 1 people found this helpful
                          • 10. Re: YoY absolute difference in market share %
                            spenser.troiano
                            spenser.troiano Feb 22, 2016 11:50 AM (in response to pooja.gandhi)

                            Perfect thanks! I'm still trying to wrap my head around filter hierarchy. As a follow-up question, the dimension filters that I'm adding to context are also used as filters on completely separate sheets that don't use LOD expressions. If I add a filter to context, will there be material affects on how that filter applies to the other sheets that don't use a LOD expression? I did a quick test of this and the other sheets that I am now using these context filters on seem to behave as expected even after I added the filters to context. Wondering if there are any hidden ramifications that I might not have caught?

                             

                            Thanks you're the best!