3 Replies Latest reply on May 8, 2017 9:02 AM by Inderpreet Kambo

Tableau Dynamic parameter with Windows_Sum to calculate Market Share

Hi Folks,
I have an issue at hand and needs urgent address and I have tried to solve it using window_sum but unable to solve it

The last year is defined as the Last date in the data-365

Previous year is defined as the (Last Year Data-365)

Change in Market Share for B (Last 12 months- Previous 12 months) for Denominator A+B is =SUM(C2:C4)/SUM(C2:C4,C7:C9)-(SUM(C5:C6)/SUM(C5:C6,C10:C11))

which is basically (Product B sold last year/product A +Product B sold last year) - (Product B sold previous year/product A +Product B sold previous year)

And for denominator A+B+C

Change in Market Share : (Product B sold last year/product A +Product B+ Product C sold last year) - (Product B sold previous year/product A +Product B +Product C sold previous year)

Any help or direction would be appreciated

Attached is the excel sheet to look into it to replicate the same calculation in Tableau

• 1. Re: Tableau Dynamic parameter with Windows_Sum to calculate Market Share

Hi 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

1 of 1 people found this helpful
• 2. Re: Tableau Dynamic parameter with Windows_Sum to calculate Market Share

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

• 3. Re: Tableau Dynamic parameter with Windows_Sum to calculate Market Share

I have one more issue Ivan

See below

The Row Total and Column total of % shows wrong