3 Replies Latest reply on Apr 1, 2014 12:30 PM by Joshua Milligan

    Need Help With Calculation (workbook attached)

    Clint Noll

      ok first post so forgive me if not enough info.

       

      when you look at my workbook(attached) you will see that i am trying to get a daily average of flight take off times. sounds simple enough, but the issue is behind the scenes i am also breaking it by pilots, among other things. the catch is that there are 2, sometimes 3, people per flight. since this is the case there could be 3 take off times associated to same flight.

       

      Day          flight     crew     seconds

      1/1/2011     1          A          10

      1/1/2011     1          B          10

      1/1/2011     1          C          10

      1/2/2011     2          A          18

      1/2/2011     2          B          18

       

      so you can see if i just avg by day that it will take all the "seconds" and average, but that wouldnt be correct. because over all avg is 13.2, but avg for flight 1 is 10, and avg for 2 is 18 and if i average these two it is 14.

       

      any help will be very appreciated

        • 1. Re: Need Help With Calculation (workbook attached)
          Joshua Milligan

          Hi Clint!

           

          Welcome to the forums!  I noticed you hadn't gotten an answer yet and took a look at your packaged workbook.  Thanks for providing it!  However, it doesn't seem to match exactly with your description (for example, there is no "crew" field).  This may have delayed getting a response.

           

          Using, just the exact data you provided in your description above, here is one way to approach getting an answer.  There are other ways to get this, but I particularly like this approach because it will give you something you can reuse for multiple visualizations.  Table calculations are another approach which I like.  The best solution for your case will depend on lots of factors, so feel free to clarify your specifics if you'd like.

           

          Proposed Solution:

           

          The idea is to duplicate your data source and then generate an extract at the correct level of detail so you can blend it back in.  In this simple example, I'm just extracting at the flight level of detail.  You might want to also include Day if you want average seconds per flight per day.

           

          After duplicating the data source, I hid every dimension except flight.  Then I changed the default aggregation of seconds to Average.  Then, when I extract the data, I select the option highlighted below:

           

          1.png

           

          (due to an issue with multiple images not getting inserted correctly, this is continued in the next post)

          • 2. Re: Need Help With Calculation (workbook attached)
            Joshua Milligan

            The resulting extract data source has a single row per flight with seconds being the average seconds:

            2.png

             

             

            (due to an issue with multiple images not getting inserted correctly, this is continued in the next post)

            • 3. Re: Need Help With Calculation (workbook attached)
              Joshua Milligan

              Now, you can use your original data set to build out the view, but bring in seconds from the secondary source and you'll have your correct average:

               

              3.png

               

              Notice how the second "seconds" field gives the right Grand Total answer!

               

              I've attached a workbook so you can see it in action.

               

              Regards,

              Joshua