8 Replies Latest reply on Jul 12, 2012 8:11 AM by Jonathan Drummey

    Aggregated attendance

    lerrence chiu

      Hi, I have table as below:

       

       

      DateTime (Hr_qtr)Count of ScanFloorScan Type
      1/1/201212-1230Entry
      1/1/1201212-2130Entry
      1/1/201212-3130Exit
      1/2/201213-2130Entry
      1/2/201214-4430Entry
      1/2/201215-1530Exit
      1/2/201211-1128Entry

       

       

      How can I calculate the aggregate view of attendance per floor per hour date?

       

      As the result the chart show:

      At 1/1/2012 Q1 of 12 pm, there are 2 person on floor 30

      At 1/1/2012 Q2 of 12 pm, there are 3 (2+1) person on floor 30

      at 1/1/2012 Q3 of 12 pm, there are 2 (2+1-1) person on floor 30

      at 1/2/2012 Q2 of 13 pm, there are 1 person on floor 30

      at 1/2/2012 Q4 of 14 pm, there are 5 (1+4) person on floor 30

      at 1/2/2012 Q1 of 15 pm, there are 0 (1+4-5) person on floor 30

      and ignore other floor like 28/F

       

      I am struggling on calculating the aggregated with perious entry

      Thanks,

       

       

       

        • 1. Re: Aggregated attendance
          Jason Scarlett

          Sounds like a table calculation of a running sum.

           

          Try creating a calculated field with the entries coded to a positive value (no change) and exits coded to a negative value. Then a running sum should give you the right value.

           

          If you post a workbook you may get the example worked out in more detail.

          1 of 1 people found this helpful
          • 2. Re: Aggregated attendance
            Jonathan Drummey

            One additional bit: once you implement Jason's suggestions, if you want to show exact totals for any given quarter hour (or even hour, possibly) then you will may need to pad out the date/times, since there are likely times that there are no entries or exits and Tableau won't draw the lines as expected (it will connect marks over the "missing" quarter hours/hours and give diagonal lines instead of the expected horizontal). If you need help with the padding, you can search the forums for "queue" and a few posts will pop up. If you still need help after that, post a packaged (.twbx) workbook with some sample data and someone here can figure something out.

            1 of 1 people found this helpful
            • 3. Re: Aggregated attendance
              lerrence chiu

              Thanks Jason and Jonathan,

               

              I see what you are saying Jonathan regarding to the "missing" quarter hour/hours.  Attached the packaged workbook with some sample data.  It will be helpful if you can so me how to do padding.

               

              Thanks,

              Lerrence

              • 4. Re: Aggregated attendance
                Jonathan Drummey

                Hi Lerrence,

                 

                You're going to need to post a packaged workbook (.twbx), the Tableau workbook you posted does not contain the data.

                • 5. Re: Aggregated attendance
                  lerrence chiu

                  Attached again.  And I actually want to sort it as follow:

                   

                  Order      HR_QTR

                  1               04-01

                  2               04-02

                  3               04-03    

                  .

                  .

                  .

                  .

                  .

                  .

                  .

                  99              03-3

                  100            03-4

                   

                  Best,

                  Lerrence

                  • 6. Re: Aggregated attendance
                    Jonathan Drummey

                    Hi Lerrence,

                     

                    Here you go. I did the following:

                     

                    - Copied out the data to a new Excel spreadsheet, called Padding Attendance.xlsx

                    - Created a 2nd worksheet in the Excel spreadsheet for all 96 values of Hr_Qtr

                    - Created a new data source in Tableau with the following Custom SQL:

                     

                    SELECT [xp].[Date] AS [Date],

                      [xp].[Hr_Qtr] AS [Hr_Qtr],

                      [xp].[Bldg] AS [Bldg],

                      [Data$].[Attendance] AS [Attendance],

                      [Data$].[CountOfEMP_ID] AS [CountOfEMP_ID],

                      [xp].[EMP_Primary_Floor2] AS [EMP_Primary_Floor2],

                      [Data$].[Hr_Qtr orig] AS [Hr_Qtr orig],

                      [Data$].[Number of Records] AS [Number of Records],

                      [Data$].[Status] AS [Status]

                    FROM (SELECT [HR QTR$].[Hr_Qtr],

                      [days].[Date],

                      [days].[EMP_Primary_Floor2],

                      [days].[Bldg]

                    FROM [HR QTR$], (SELECT [Data$].[Date] AS [Date],

                      [Data$].[EMP_Primary_Floor2] AS [EMP_Primary_Floor2],

                      [Data$].[Bldg] AS [Bldg]

                    FROM [Data$]

                    GROUP BY [Data$].[Date], [Data$].[EMP_Primary_Floor2], [Data$].[Bldg]) AS [days]) AS [xp]

                    LEFT JOIN [Data$] ON [Data$].[Date] = [xp].[Date] AND [Data$].[Hr_Qtr] = [xp].[Hr_Qtr]

                     

                    There are three nested queries within this:

                    1. Get the combination of all days, floors, and buildings in the data

                    2. Do a cross product with the results of the first query and the Hr_Qtr table to generate a combination of all days, floors, buildings, and possible Hr_Qtr values

                    3. Take all those rows, and left-join the rest of the fields from the original data to them

                     

                    I used IF instead of nested IIF statements in the Attendance calc, it's easier to understand that way, also IF statements are computed faster than IIF in Tableau, according to Tableau tech support.

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 7. Re: Aggregated attendance
                      lerrence chiu

                      Thanks Jonathan,

                       

                      This is super helpful.  Now I have to understand what you have been done on the Custom SQL. Can you explain to me what the [xp] is doing?

                       

                      Lerrence

                      1 of 1 people found this helpful
                      • 8. Re: Aggregated attendance
                        Jonathan Drummey

                        The [xp] is an alias to the results of the 2nd query, [xp] meaning "x product" or "cross product". In SQL statements, both field names and table names (and the results of a query that return a table) are aliased using the [original name] AS [new name] format.

                         

                        Jonathan