1 2 Previous Next 19 Replies Latest reply on Jun 25, 2019 8:49 AM by Lee Polikoff

# Calculation for Average of an Average over 2 or More Different Periods

I am working on creating calculations for Statistical Process Control charts (specifically a P chart). I need to determine an average of an average over 2 different portions of my dataset. I've attached a workbook with sample data. Specifically, I need to calculate the average of the monthly averages for 2 different time periods. The first is from the start of the data set thru Dec 2016. The second is from Jan 2017 thru the end of the dataset. Here is a screen shot of what I am trying to do (the bottom is from Excel). Any assistance would be greatly appreciated.

• ###### 1. Re: Calculation for Average of an Average over 2 or More Different Periods

Find the attached workboook BR,

NB

• ###### 2. Re: Calculation for Average of an Average over 2 or More Different Periods

See attached.

• ###### 3. Re: Calculation for Average of an Average over 2 or More Different Periods

Naveen kind of did the same thing I did, only h hard-coded the break between 2016 and 2017.

I made a parameter that lets you pick a year where that break occurs.

and I used table calcs (window_avg) to grab averages by year and by phase.  I used "restart every" to get the by-year and by-phase averages.  (I did a yearly average too, just to show how that would look.)

• ###### 4. Re: Calculation for Average of an Average over 2 or More Different Periods

Joe Oppelt Naveen B - Thank you BOTH for providing a solution (within minutes of each other I might add). You both took different approaches which I'm trying to understand. Here are the questions I have:

Joe - I see that you used table calculations and restarting every phase.

1. I don't understand the phase boundary parameter. How does a phase boundary of 2,016 relate to the date the phase changes?

2. What would I do if I had more than 2 phases?

Naveen - Wow, that is some calculation.It's making my head spin. I understand the main IF part (date greater or less than 12/2016), however I'm getting lost in the LOD calculation. Can you explain the following?

{Fixed :AVG(

{fixed DATE(DATETRUNC('month', [Flight Month])):

SUM(IF DATE(DATETRUNC('month', [Flight Month])) >= DATE({MIN(DATETRUNC('month', [Flight Month]))})

and DATE(DATETRUNC('month', [Flight Month]))  <= DATETRUNC('month', #2016-12-01#)

THEN

[Monthly Avg]

END)

}

Thanks again to both of you

• ###### 5. Re: Calculation for Average of an Average over 2 or More Different Periods

Inner Lod:

{fixed DATE(DATETRUNC('month', [Flight Month])):

SUM(IF DATE(DATETRUNC('month', [Flight Month])) >= DATE({MIN(DATETRUNC('month', [Flight Month]))})

and DATE(DATETRUNC('month', [Flight Month]))  <= DATETRUNC('month', #2016-12-01#)

THEN

[Monthly Avg]

END)

Divided to 2 steps:

i) If calculation

If calcualtion is checking the date range between and pulling the monthly average

ii) {Fixed calculation}

Sum the monthly average values

Innner calcualtion will give the o/p like below

Month Monthly Avg

sep          10

oct          11

Nov          12

outer lod

i am taking the average overall without any date granularity based on above example

10+11+12/3 = 11

this value will show in all the fields

then using IF condition on top of it to show only data up to selected month

in your scenario up to Dec 206

Hope this helps

BR,

NB

• ###### 6. Re: Calculation for Average of an Average over 2 or More Different Periods

Ok, I think I got it. Is there a way to change the date (i.e. #2016-12-01#) into a parameter? I may need to have more than 2 phases or I may want to change my phase dates on the fly. I realize that if I had more than 2 phases I would have to add in another elseif.

• ###### 7. Re: Calculation for Average of an Average over 2 or More Different Periods

replace that condition with date parameter it will do the trick

BR,

NB

• ###### 8. Re: Calculation for Average of an Average over 2 or More Different Periods

Naveen B Joe Oppelt I'm going to throw a wrench into my question. Originally I gave you a sample with the counts already calculated. The underlying data was formatted as Flight Month, Flights On-Time, Total Flights.  This was the wrong question/dataset. The actual data has individual data points (Flight Month, Flight Status). The individual calculations need to be aggregated. I can get the monthly values, but from there I can't implement what you provided because I get the issue of doing aggregations of aggregations. I discovered this when I tried to implement your solutions on my real dataset. I've attached an updated sample (see sheet 2). I was hoping you can take some more time to further assist me.Thanks in advance.

• ###### 9. Re: Calculation for Average of an Average over 2 or More Different Periods

Lee Polikoff wrote:

Joe - I see that you used table calculations and restarting every phase.

1. I don't understand the phase boundary parameter. How does a phase boundary of 2,016 relate to the date the phase changes?

2. What would I do if I had more than 2 phases?

It wasn't clear how your phases were determined.  Your screen shot just showed the break at the end of Year 2016.  So I made a parameter that lets you set the year that ends the first phase. Then I just made a dimension that added different values for P1 and P2.  And I set the param to 2016 for that.

If you have multiple boundaries, there must be some logical determination of that.  You'd want to make some dimension to specify the 3 (or 4 or 5, whatever) different phases.  The RESTART EVERY method I used would just work on that dimension.  (Just like you can see how the yearly average works against the YEAR(date) dimension.)  Maybe you would have x-many parameters (if it's up to the user to set the boundaries.)  Or you'd have some field added in your data source itself that would delineate the phases.  How you get those dimension values set is going to be determined by your business needs.

• ###### 10. Re: Calculation for Average of an Average over 2 or More Different Periods

Lee Polikoff wrote:

Naveen B Joe Oppelt I'm going to throw a wrench into my question. Originally I gave you a sample with the counts already calculated. The underlying data was formatted as Flight Month, Flights On-Time, Total Flights. This was the wrong question/dataset. The actual data has individual data points (Flight Month, Flight Status). The individual calculations need to be aggregated. I can get the monthly values, but from there I can't implement what you provided because I get the issue of doing aggregations of aggregations. I discovered this when I tried to implement your solutions on my real dataset. I've attached an updated sample (see sheet 2). I was hoping you can take some more time to further assist me.Thanks in advance.

I used window_avg to get averages of aggregations.  Table calcs are how you do aggs of aggs.  And you can do table calcs on table calcs.

I'll take a look at your latest.

What version of Tableau are you using?

• ###### 11. Re: Calculation for Average of an Average over 2 or More Different Periods

Here you go attached changing the aggregation

Formula:

MIN(IF DATE(DATETRUNC('month', [Flight Month])) >= DATE({MIN(DATETRUNC('month', [Flight Month]))})

and DATE(DATETRUNC('month', [Flight Month]))  <= DATETRUNC('month', #2016-12-01#)

THEN

{Fixed :AVG(

{Fixed DATETRUNC('month', [Flight Month]): SUM(

{fixed DATETRUNC('month', [Flight Month]):SUM(IF DATE(DATETRUNC('month', [Flight Month])) >= DATE({MIN(DATETRUNC('month', [Flight Month]))})

and DATE(DATETRUNC('month', [Flight Month]))  <= DATETRUNC('month', #2016-12-01#) THEN [Flights OnTime] END)}/

{fixed DATETRUNC('month', [Flight Month]):SUM(IF DATE(DATETRUNC('month', [Flight Month])) >= DATE({MIN(DATETRUNC('month', [Flight Month]))})

and DATE(DATETRUNC('month', [Flight Month]))  <= DATETRUNC('month', #2016-12-01#) THEN [Total Flights] END)}

)}

)

}

ELSEIF

DATE(DATETRUNC('month', [Flight Month]))  > DATETRUNC('month', #2016-12-01#)

THEN

{Fixed :AVG(

{Fixed DATETRUNC('month', [Flight Month]): SUM(

{fixed DATETRUNC('month', [Flight Month]):SUM(IF DATE(DATETRUNC('month', [Flight Month]))  > DATETRUNC('month', #2016-12-01#) THEN [Flights OnTime] END)}/

{fixed DATETRUNC('month', [Flight Month]):SUM(IF DATE(DATETRUNC('month', [Flight Month]))  > DATETRUNC('month', #2016-12-01#) THEN [Total Flights] END)}

)}

)

}

Hope this helps

BR,

NB

1 of 1 people found this helpful
• ###### 12. Re: Calculation for Average of an Average over 2 or More Different Periods

I'm using 2019.1.0

I just finished reading your last response. I feel like an idiot. I didn't get that 2,016 was 2016. I need more coffee.

Thanks

• ###### 13. Re: Calculation for Average of an Average over 2 or More Different Periods

Just want to clarify something.

You can do the average of those monthly averages with window_avg, but I think what you really want to do is a window_sum(sum(on time))/window_sum(sum(total flights)).  Those two operations won't result in the same value.  (Usually.  They'll be close, but they'll differ.)

So what do you want as your determination of Phase-1 vs Phase-2?  (Or do you just want to do the average of the whole set for now?)

• ###### 14. Re: Calculation for Average of an Average over 2 or More Different Periods

In the attached I did the average both ways for the whole data set.  You can see what I mean about the values being different.

1 2 Previous Next