-
1. Re: YoY absolute difference in market share %
Shinichiro MurakamiFeb 11, 2016 4:04 PM (in response to spenser.troiano)
1 of 1 people found this helpfulChange 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
-
2. Re: YoY absolute difference in market share %
spenser.troiano Feb 11, 2016 5:23 PM (in response to Shinichiro Murakami)1 of 1 people found this helpfulThanks 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??
-
3. Re: YoY absolute difference in market share %
Shinichiro MurakamiFeb 11, 2016 10:02 PM (in response to spenser.troiano)
1 of 1 people found this helpfulYou 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
-
4. Re: YoY absolute difference in market share %
Shinichiro MurakamiFeb 12, 2016 5:18 AM (in response to Shinichiro Murakami)
-
5. Re: YoY absolute difference in market share %
pooja.gandhi Feb 12, 2016 5:23 AM (in response to spenser.troiano)1 of 1 people found this helpfulSpenser,
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.
-
6. Re: YoY absolute difference in market share %
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 MurakamiFeb 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 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
Pooja Gandhi who have helped on this.
Thanks!!
-
9. Re: YoY absolute difference in market share %
pooja.gandhi Feb 22, 2016 8:54 AM (in response to spenser.troiano)1 of 1 people found this helpfulHi 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:
-
10. Re: YoY absolute difference in market share %
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!