
1. Re: Tableau Dynamic parameter with Windows_Sum to calculate Market Share
Ivan Young Feb 23, 2017 2:43 PM (in response to Inderpreet Kambo)1 of 1 people found this helpfulHi Inderpreet,
I would start by creating a dimension for LY and PY and measures for LY and PY Viles. Maybe use a fixed LOD instead of WindowSum. If your req solution you look to have 'Product C' filtered out which would affect the window sum.
For the LY or PY Dimension first create a field for your Max Date: { FIXED : MAX([Date]) }
Then you can create your LY or PY Dim:
IF DATEDIFF('day', [Date],[Max Date]) <= 365 THEN 'LY' ELSEIF DATEDIFF('day', [Date],[Max Date]) > 365 AND DATEDIFF('day', [Date],[Max Date]) <= 730 THEN 'PY' END
Then create measures for LY and PY
LY Vials: IF [LY or PY] = 'LY' THEN [Vials] END
PY Vials: IF [LY or PY] = 'PY' THEN [Vials] END
With these measures you can create your LY Market Share and PY Market Share
LY Market Share: [LY Vials]/{ FIXED : (SUM(IF [LY or PY] = 'LY' THEN [Vials] END)) }
PY Market Share: [PY Vials]/{ FIXED : (SUM(IF [LY or PY] = 'PY' THEN [Vials] END)) }
Then to calculate the change in Market Share:
Change in MS: SUM([LY Market Share])  SUM([PY Market Share])
There are a few ways you could use a parameter to make the denominator user controlled. A parameter with the values of 'Product C' and '' could probably do the trick, you would adjust the formulas [LY Vials]/{ FIXED : (SUM(IF [LY or PY] = 'LY' and [Product] != [Parameter] THEN [Vials] END)) }
Let me know if you have any questions.
Regards,Ivan

2. Re: Tableau Dynamic parameter with Windows_Sum to calculate Market Share
Inderpreet Kambo Feb 23, 2017 5:26 PM (in response to Ivan Young)Awesome Ivan. I really liked how you explained the calculations and go about it.
I just made some minor changes to the calculations and it did what I wanted.
This helped a lot.
5 thumps up Ivan