1 2 Previous Next 16 Replies Latest reply on Sep 9, 2013 8:50 AM by Stanley McLenna

    Count Occurances of Measure Values

    Stanley McLenna

      I originally posted a thread asking how to display the minimum datetime value for each day (http://community.tableau.com/thread/130495) however that was just one piece of the solution. I can get the data I need just by placing measures and dimensions in the Rows panel so I can list data before I start building charts and graphs. Here is the data I can see:

      compliant_data.JPG.jpg
      My ultimate goal is to display a stacked bar graph that counts the total occurrences of the Compliant/Not-Compliant in the image above. Every attempt I've tried will create a stacked bar graph, however the bars will be split so I have a group of compliant, then a group of non-compliant, and then some more compliant entries. It doesn't have to be a stacked bar graph either, it can be two bars for each user, I just need to see the total number of either compliant or non-compliant values. I considered Table Calculations but I don't understand Table functions like RUNNING_ and WINDOW_ functions.

        • 1. Re: Count Occurances of Measure Values
          Joshua Milligan

          Stanley,

           

          What is the calculation for the field on the Text shelf?  Do you happen to have a packaged workbook you can share (even with mockup data)?


          Regards,

          Joshua

          • 2. Re: Count Occurances of Measure Values
            Alex Kerin

            Stanley, I work with punch data and schedule data every day in Tableau, so have got to know some tricks.

             

            I'm assuming that each of those rows you are showing are effectively rows in the database? Or multiple rows (i.e. punch on one row, schedule on another?)

            • 3. Re: Re: Count Occurances of Measure Values
              Stanley McLenna

              Thanks for both of your replies, I've attached a workbook with some mochup data. I've taken the report as far as I can; I just need to count the occurrences of compliant/non-compliant per user and display that information, preferably in a stacked bar graph.

              • 4. Re: Re: Count Occurances of Measure Values
                Joshua Milligan

                Alex,

                 

                If you want to take this one, go for it!  Otherwise, please let me know and I'll take a look.

                 

                Regards,

                Joshua

                • 5. Re: Count Occurances of Measure Values
                  Alex Kerin

                  No problem. working on it - you have a very similar problem that I have all the time - multiple punches during a single shift. The reason you cannot just use a sum([number of records]) is because your min([punch]) stops working.

                   

                  There are a number of ways to deal with this. Some Q's:

                   

                  • Do shifts ever run over midnight?
                  • How many rows of data are we talking?
                  • What is the source of the data (excel, SQL)?
                  • 6. Re: Count Occurances of Measure Values
                    Alex Kerin

                    Thanks Joshua - it makes sense. I dream about missed punches...

                    • 7. Re: Count Occurances of Measure Values
                      Alex Kerin

                      Also, where do you want to to from here - is this a one-off, will you be building other metrics?

                      • 8. Re: Count Occurances of Measure Values
                        Joshua Milligan

                        That's funny!  I look forward to seeing your solution!

                        --Joshua

                        • 9. Re: Count Occurances of Measure Values
                          Stanley McLenna

                          To answer your questions:

                          1. No, shifts do not run over midnight
                          2. So far, the data volume is maybe a couple thousand records for a month's worth of data; pretty small
                          3. Source is a CSV file (not ideal); it's a data dump from Five9's and to my knowledge, Tableau doesn't connect directly to that service

                          I don't plan on using this data for anything other than compliance/non-compliance charting; just getting it to a chart would be helpful.

                          • 10. Re: Count Occurances of Measure Values
                            Alex Kerin

                            Understood, can you explain the criteria for a non compliant? At the moment you add a minute to the schedule, then if the first punch is before that time, it's compliant - in other words, as long as they are not more than a minute late, it's compliant?

                            • 11. Re: Count Occurances of Measure Values
                              Alex Kerin

                              And based on that, presumably you want to filter out the punch blocks that are after a break, otherwise the non-compliant count will be too high?

                              • 12. Re: Count Occurances of Measure Values
                                Stanley McLenna

                                A punch is considered non-compliant when the first punch of the day is one minute after or later than their expected start time. A punch is compliant when it's before one minute after their scheduled start time. We only care about the first punch for the day, so no breaks are tracked. Make sense?

                                • 13. Re: Re: Count Occurances of Measure Values
                                  Alex Kerin

                                  So here's the approach I took. Create a Shift ID so that all punches for a day fall under one identifier. Because your shifts never go over midnight, this is simply user number plus date (no time).

                                   

                                  Create a punch block that increments for each punch block of the shift. I can use index() here. index() is a table calculation that basically provides a rank. Because of this I must set the partitioning (the index must reset every shift ID) and the order (increment starting with the first punch)

                                   

                                  punch.png

                                  I do that through right clicking the pill, edit table calculation, advanced. However you use this in the future, shift ID must be the only thing in the left box, and the sort order must be set as shown. Every time you use this elsewhere, you have to set this up.

                                   

                                  Then we can use this to filter - punch block 1 will always be the first of the day. I also simplified your compliant calc:

                                   

                                  IF [Login TimeStamp Converted]<dateadd('minute',1,[Scheduled Start DateTime]) THEN

                                      "Compliant"

                                  ELSE

                                      "Not Compliant"

                                  END

                                   

                                  Please don't hesitate to ask any other questions

                                  • 14. Re: Re: Re: Count Occurances of Measure Values
                                    Stanley McLenna

                                    I'm not sure what this accomplished; am I able to count the number of compliant/non-compliant occurrences from your example? I couldn't figure out a way to do it and then display that in a bar graph. I've been researching other methods to get the same results and the custom SQL editor seems to be the most promising, however I can't do the SQL aggregations that would help me the most. Here's the custom SQL I'm trying to implement:

                                    SELECT [audience_care_member_start_time#csv].[AGENT FIRST NAME] AS [AGENT FIRST NAME],

                                      [audience_care_member_start_time#csv].[AGENT FULL NAME] AS [AGENT FULL NAME],

                                      [audience_care_member_start_time#csv].[AGENT LAST NAME] AS [AGENT LAST NAME],

                                      [audience_care_member_start_time#csv].[DATE] AS [DATE],

                                      [audience_care_member_start_time#csv].[LOGIN TIMESTAMP] AS [LOGIN TIMESTAMP],

                                      [audience_care_member_start_time#csv].[STATE] AS [STATE],

                                      MIN([audience_care_member_start_time#csv].[TIME]) AS [TIME],

                                      [Agent number assignments - PRIVATE#csv].[Agent #] AS [Agent number assignments - PRIVATE#csv_Agent #],

                                      [Agent number assignments - PRIVATE#csv].[Agent Name] AS [Agent number assignments - PRIVATE#csv_Agent Name],

                                      [Agent number assignments - PRIVATE#csv].[AgentName Upper] AS [Agent number assignments - PRIVATE#csv_AgentName Upper],

                                      [Agent number assignments - PRIVATE#csv].[Start Time] AS [Agent number assignments - PRIVATE#csv_Start Time]

                                    FROM [audience_care_member_start_time#csv]

                                      INNER JOIN [Agent number assignments - PRIVATE#csv] ON [audience_care_member_start_time#csv].[AGENT FULL NAME] = [Agent number assignments - PRIVATE#csv].[AgentName Upper]

                                    GROUP BY [audience_care_member_start_time#csv].[DATE]

                                    ORDER BY [audience_care_member_start_time#csv].[AGENT LAST NAME],[audience_care_member_start_time#csv].[DATE],[audience_care_member_start_time#csv].[TIME]

                                    The part that breaks it is the MIN() function on the TIME column; it returns an error like can't recognize [AGENT FULL NAME] or something. This JOIN's data from two CSV files right now, so would that be preventing me from using the MIN() aggregation on the data? If I can just have the SQL choose the minimum time based on the day, I'm good and can build the charts I need.

                                    1 2 Previous Next