3 Replies Latest reply on Nov 27, 2018 8:57 AM by Don Wise

    How to Calculate Confidence Level, Standard Deviation, Weighted Response, P-value Dynamically in Tableau

    Mahesh Gujar

      Hi Team,

       

       

      I'm trying to calculate Confidence Level, Standard Deviation, Weighted Response, P-value which we can do with ease in excel as we can pick cell to cell value. But, I'm running short of ideas to get these calculations in my work book dynamically.

       

       

       

      I would really appreciate your help if you can help me to get these calculations in tableau.

       

       

       

       

      Attached is the sample data source. Please let me know if you need any additional information.

       

       

      End Goal is to Calculate Confidence Level and to get it we need to calculate:

      • Conversion Lift
      • Weighted Response
      • Standard Deviation
      • P-value
      • Confidence Level - Final Output

       

      Thank you!!

        • 1. Re: How to Calculate Confidence Level, Standard Deviation, Weighted Response, P-value Dynamically in Tableau
          Don Wise

          Hello Mahesh,

          I was only able to get you partially there. Had to stop at the Standard Deviation calculation as I wasn't arriving at nearly the same values that MS-Excel is producing, even when mimicking the calculation out of Excel.  Perhaps someone else will also see this post and see something that I don't...I tried a number of different scenarios and table calcs and didn't come close to those that you need...sorry! I was able to get you Weighted Response & Conversion Lift; both of which, required some additional table calc settings (you should look at them to see how they're set).

           

          Regardless, attached is a 2018.3 workbook and below are some sample screenshots to help you (or others along):

           

          Conversion Lift:

          Screen Shot 2018-11-26 at 9.51.59 AM.png

          Weighted Response:

          Screen Shot 2018-11-26 at 10.26.14 AM.png

          This is the STDEV calculation I did to mimic the MS-Excel version, with unequal results:

          Screen Shot 2018-11-26 at 12.24.08 PM.png

          This version of STDEV calculation using Tableau Function matches that of several calculators:

          Screen Shot 2018-11-26 at 12.24.34 PM.png

          • 2. Re: How to Calculate Confidence Level, Standard Deviation, Weighted Response, P-value Dynamically in Tableau
            Mahesh Gujar

            Thank you so much Don Wise. Really appreciate your help.

             

            This is a ray of light for me!!

             

            With current value of Standard Deviation, could you please show me a way to calculate p-value and confidence level.

             

            I will work on Standard Deviation value till then, and will let you know if I come across anything.

             

            Thanks a ton, Don.

            • 3. Re: How to Calculate Confidence Level, Standard Deviation, Weighted Response, P-value Dynamically in Tableau
              Don Wise

              Hello Mahesh,

               

              Please see newly attached 2018.3 workbook.  I've added in a P-Value and Confidence Level, however, not sure they're correct as Confidence Level relies on P-Value. 

               

              P-Value is calculated based off of Tableau's Z-Score calculation Calculate Z-scores - Tableau so please read that thread for some understanding.

               

              The P-Value calculation in your Excel file is using TDIST (Student Distribution Score); which returns the Percentage Points (probability) for the Student t-distribution where a numeric value is a calculated value of t for which the Percentage Points are to be computed. The t-distribution is used in the hypothesis testing of small sample data sets.  In Tableau, TDIST doesn't exist in terms of an equal equivalent. 

               

              However, I did locate the following thread where a P-Value was calculated using Tableau's Z-Score methodology: To achieve T.Dist() function of Excel in Tableau to get the p-value of a T-Test  So, hopefully you're getting the values you need. Not sure due to the amount of data involved here which is very small. Hopefully your real data set is a larger set of data?  The Confidence Level is fairly simple after that which is: 1-[P-Value] formatted as percentage, per your Excel file. 

               

              Again, my results don't match yours whatsoever; of which, goes back to the original method in the Excel worksheet for Standard Deviation and how that's being calculated. I'm using Tableau's results for Standard Deviation, which is why my numbers are different from yours.  Each of your results from Left-to-Right are contingent upon each other until the final Confidence Level value.

               

              Hope it helps!  Thx, Don

              Screen Shot 2018-11-27 at 8.48.15 AM.png