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

    Tableau Dynamic parameter with Windows_Sum to calculate Market Share

    Inderpreet Kambo

      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
          Ivan Young

          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
            Inderpreet Kambo

            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
              Inderpreet Kambo

              I have one more issue Ivan

              See below

               

              The Row Total and Column total of % shows wrong