Running Product

Version 2

    Description:

     

    Tableau has a formula for running sum (RUNNING_SUM), but not for running product.  A running product could be useful, for example, in calculating the compounding effects of inflation or interest over time when the rate is not constant for each time period.

     

    The formula takes advantage of the following rules involving logarithms:

     

    For x > 0 and y > 0:

     

    By the inverse property rule:  xy = eln(xy)

                   

    By the product rule:  ln(xy) = ln(x) + ln(y)

     

    Putting the two together:  xy = e(ln(x) + ln(y))

     

    Example Calculation:

     

    //The formula is developed for three scenarios that depend on the range of your data.  The final formula is the most complex, but will work for all real numbers.  In the formula I use SUM([Data1]), but this could be replaced by any aggregate measure.

     

    //If all data sums are positive, then the formula is quite elegant:

     

    If all SUM([Data1]) > 0, then

     

    Running Product = EXP(RUNNING_SUM(LN(SUM([Data1]))))

     

    //If both positive and zero are possible, then we add a factor to make the running product zero once the zero data sum is encountered:

     

    If all SUM([Data1]) >= 0, then

     

    Running Product =

    EXP(RUNNING_SUM(LN(SUM([Data1]))))

    * IIF(RUNNING_MIN(ABS(SUM([Data1])))=0,0,1)

     

    //If negative numbers are possible in addition to positive and zero, then we need to add ABS() around the data sum in the first part, and introduce a third factor that flips the sign when a negative data sum is encountered:

     

    If SUM([Data1]) can be positive, negative, or zero, then

     

    Running Product =

    EXP(RUNNING_SUM(LN(ABS(SUM([Data1])))))

    * IIF(RUNNING_MIN(ABS(SUM([Data1])))=0,0,1)

    * IIF(RUNNING_SUM(SUM(IIF([Data1]<0,1,0))) % 2 = 0,1,-1)

     

     

    Inputs and Setup: (for table calculations, delete if not applicable)

    Input is any aggregate measure (e.g. SUM(Data1)), and the dimension must be displayed.

    Partitioning and Addressing: (for table calculations, delete if not applicable)

    Partitioning and addressing should work the same way as they do for RUNNING_SUM.

    Comments:

     

    Related Functions:

     

    RUNNING_SUM

     

    Further Reading/Examples:

    I've attached a packaged Tableau workbook with an example.