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

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

• ###### 1. Re: Problems with Calculating 3 Month/6 Month Averages from Calculated Field

Look at WINDOW_SUM and WINDOW_AVG.  You can specify how far back to add/average, and where to start.

For instance,

WINDOW_SUM(SUM([Items]),-4,-1)

this says to sum up SUM([Items]) from 4 cells prior to the current cell through the prior cell.  (So if you are in September, this will add up May, June, July and August.)

This will allow you to plug in a field name or a parameter in those offset values, and if you design things right, one calc can handle whatever condition the users asks for on the sheet.

• ###### 2. Re: Problems with Calculating 3 Month/6 Month Averages from Calculated Field

Joe,

Thanks for the advice. My issue is my field that I would be plugging in is based on a calculated field already, I am running to an error, any suggestion?

• ###### 3. Re: Problems with Calculating 3 Month/6 Month Averages from Calculated Field

WINDOW_SUM can sum up calculated fields.  I do it all the time.

At this point it would really serve both of us well if you could upload a sample workbook.  I can show you what you need to do.  And I can see what the error is.  ("I'm running into an error" doesn't tell me much...)