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

**William Marshall**Jun 12, 2017 7:14 AM

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