5 Replies Latest reply on Apr 29, 2016 12:25 AM by Ashish Chaudhari

    Average and aggregate

    Boreak Silk

      Hello All,

       

      I am very new to Tableau and have a very simple question - how to calculate averages and then aggregate the averages by day, time, location and business type? I have a list of businesses with location (Area_ID), business type (BusinessType_ID), opening day (Day) and time (Time), find attached data and screenshot of the data below. I want to know, let say, how many businesses are open between Friday 5pm and Saturday 3am in Area 4143 and business type 2. I thought of the following steps:

       

      1. calculate averages which will be 1 for those businesses that are open during the above period

      2. add them all up to get the total number of businesses.

       

      I'm I on the right track? How do I do it in Tableau so that I can get correct results whenever I change day, time, location and/or business type?

       

      Attached is sample data. The 'Data' field contains only value of 1 representing the business is open.

       

      Thank in advance for any assistance.

       

      Regards

       

      Bo

       

        • 1. Re: Average and aggregate
          Rishabh Dhingra

          Hi Boreak,

           

          I have a quick question on the data:

           

          The 'Data' measure value is greater than 1 in many cases, example:- For area code 4143, Business Type Id - 6, Day - Monday, the data value is 7, 8 , 9 etc. Could you help me in understanding this measure? What does it signify?

           

          Thanks,

          Rishabh

          • 2. Re: Average and aggregate
            Boreak Silk

            Hi Rishabh,

            Thanks for your prompt response. I double checked and the max and min value of

            the ‘Data’ column is 1. I am not sure if I’ve missed something.

             

            Only the ‘Time’ column has values from 1 to 24.

             

             

            Kind regards

             

            Boreak

            • 3. Re: Average and aggregate
              Rishabh Dhingra

              Hi Boreak,

               

              Please find below the snapshot. Let me know if I am missing something:

               

              Businesses.JPG

              • 4. Re: Average and aggregate
                Boreak Silk

                Hi Rishabh,

                 

                Those numbers are sums of values in data column for the Area_ID = 4143, BusinessType_ID = 6 Day = Monday.

                In other words (eg. for the first row of your screenshot), there are 7 businesses open at 0000-0100 on Monday

                in Area_ID 4143 and they all are in BusinessType_ID 6.

                 

                Hope this explains.

                 

                 

                Kind regards

                 

                Boreak

                • 5. Re: Average and aggregate
                  Ashish Chaudhari

                  Hi Boreak,

                   

                  Please follow the steps, to calculate the averages by any dimension

                  Create the chart the way you do, then for "Data", change the aggregation to average.

                   

                  If you require averages by multiple dimensions then add all those dimension to the rows and change the aggregation to average (refer to the above image) .

                   

                   

                  Since you maximum and minimum are 1, you will get result of average as 1.

                   

                  PS - To learn more about aggregation, please refer to the below mentioned link

                  Aggregating Data

                   

                  Thanks and Regards,

                  Ashish Chaudhari