3 Replies Latest reply on Jun 12, 2017 8:30 AM by Joe Oppelt

    Problems with Calculating 3 Month/6 Month Averages from Calculated Field

    William Marshall

      I am hoping someone might be able to help me with the requirement I have been asked to satisfy for my end user. We have been asked to provide the ability to show/calculate the Previous Month Average, 3 Month Average and 6 Month Average at three different levels of aggregation for a calculated rate based on another field. I have attempted to use Last Function and other items to accomplish this but run into calculation issues.  Below is breakdown of what has been attempted to date.

       

      Business Problem: Cannot accurately show Previous Month Average, 3 Month Average and 6 Month Average at different levels of aggregation.  Aggregation at Level 1 is accurate because of complete data, whereas Level 2/3 presents incomplete data and calculations are incorrect.  Unsure how to accurately capture Previous Month Average, 3 Month Average and 6 Month Average.

       

      Data Source/Structure and Calculations

       

      Data Source: Microsoft Excel

      Tableau 10.0

       

      Original Calculations to create column which is used within chart

       

      No Indicator: if  Dimension = "N" AND  Measure Value >= 1 THEN 1 ELSE 0 END

      Yes Indicator: if  Dimension = "Y" AND  Measure Value >= 1 THEN 1 ELSE 0 END

      Total: Sum(No Indicator)+ Sum (Yes Indicator)

      Compliance Rate (%): Sum(No Indicator)/ Total

       

      Levels of Aggregation

       

      Level 1: Parent (Example: Commercial Banking)

       

      Level 2: Child (Example: Commercial Banking Sales)

       

      Level 3: Grand Child (Example: Commercial Banking Sales  - Ontario)

       

      *  Level 1 presents complete data because of contributions of Level 2 and Level 3, however when attempting to calculate based on Level 2 and Level 3 and with missing data we cannot get calculations to work properly.

       

       

      Previously Attempted Calculations for Average Fields

       

      Last Month Value: LOOKUP(([Compliance Rate]),LAST())

       

       

      Last 3 Month Average:

      ( LOOKUP(

       

       

      (ZN

        (SUM

              ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10) - N])/

                 ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10)]] - Total]))),LAST()-2)

      +

      LOOKUP(

       

       

      (ZN

        (SUM

              ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10) - N])/

                 ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10)]] - Total]))),LAST()-1)

      +

       

       

      LOOKUP(

      (ZN

        (SUM

              ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10) - N])/

                 ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10)]] - Total]))),LAST())

      )

      /3

      Last 6 Month Average:

      (

      LOOKUP(

      (ZN

        (SUM

              ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10) - N])/

                 ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10)]] - Total]))),LAST()-5)

      +

      LOOKUP(

      (ZN

        (SUM

              ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10) - N])/

                 ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10)]] - Total]))),LAST()-4)

      +

      LOOKUP(

      (ZN

        (SUM

              ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10) - N])/

                 ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10)]] - Total]))),LAST()-3)

      +

      LOOKUP(

      (ZN

        (SUM

              ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10) - N])/

                 ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10)]] - Total]))),LAST()-2)

      +

      LOOKUP(

       

       

      (ZN

        (SUM

              ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10) - N])/

                 ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10)]] - Total]))),LAST()-1)

      +

      LOOKUP(

      (ZN

        (SUM

              ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10) - N])/

                 ([Flag (# BD between 10 BD after Discovery Mth End &  Create Date > 10)]] - Total]))),LAST())

      )

      /6