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

    Lee Polikoff

      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).

       

      Tableau.jpg

      Any assistance would be greatly appreciated.

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

          Find the attached workboook

           

           

          Hope this helps plz mark this answer as correct & helpful to close the thread

           

          BR,

          NB

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

            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
              Lee Polikoff

              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
                Naveen B

                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

                Plz mark this answer as correct & helpful to close the thread

                 

                 

                BR,

                NB

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

                  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
                    Naveen B

                    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
                      Lee Polikoff

                      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
                        Joe Oppelt

                        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
                          Joe Oppelt

                          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
                            Naveen B

                            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

                            Plz mark this answer as correct & helpful to close the thread

                            BR,

                            NB

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

                              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
                                Joe Oppelt

                                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
                                  Joe Oppelt

                                  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