7 Replies Latest reply on Mar 29, 2012 6:39 AM by Ian Waring

    Mental block: count of items with an aggregated measure?

    Ian Waring

      I have a list of tasks and how many minutes a person is spending on each one. Some tasks are touched by multiple people in the team.

       

      Task# Person Minutes

      134 Joe 60

      134 Ian 95

      134 Fred 22

      135 Joe 12

      136 Ian 22

      137 Fred 55

      137 Joe 10

       

      I build a view of Task#, sum(minutes)

       

      Task sum(minutes)

       

      134 177

      135 12

      136 22

      137 65

       

      Now, I want to graph the number of SLAs achieved if the SLA was that the folks in the group spent no more than 60 minutes total on each task. So if the above was the data for one month, 2 of the tasks would have been in SLA, 2 outside SLA

       

      if I define a calculation of Met_SLA as being sum(minutes) <= 60, i get:

       

      Task sum(minutes) Met_SLA

       

      134 177 Missed

      135 12 In SLA

      136 22 In SLA

      137 65 Missed

       

      I have a mental block trying to display that without ending up totalling all the tasks together and the combined total showing a total of (177+12+22+65) - hence one total SLA loss.

       

      Any ideas how to display the Counts of SLA achieves vs misses in a form that retains the integrity of the minutes taken in each task?

       

      Ian W.

        • 1. Re: Mental block: count of items with an aggregated measure?
          Alex Kerin

          I'm not convinced this is the best answer as I'm not sure if it could be done more easily or robustly. I have used two table calculations. One to sum for each ID, and then another to sum these results. You can see the partitioning I have set for both.

          1 of 1 people found this helpful
          • 2. Re: Mental block: count of items with an aggregated measure?
            Ian Waring

            Really appreciate this Alex. However, I am now trying to calculate the SLA percentage of "in SLA"/"Total #Tasks" for each team member, hence the 1 or 0 then SUM won't work it with that new scenario. Every time I try, I get text overlap city.

             

            Think i'm being really dumb on how the Level of Detail shelf works. Think I need to go find some resources that labour that with me...

            • 3. Re: Mental block: count of items with an aggregated measure?
              Alex Kerin

              It's not the Level of Detail that you need more help on per se, it's the setting of the partitioning on the Table Calc. Here's the solution I think you are looking for. I would guess that using a set here would help, but that's beyond my knowledge. Joe Mako or Richard Leeke may be able to chime in.

               

              When I think of the three equations, here's how I worked them out:

               

              Did we meet SLA? Name is irrelevant, ID is how we identify each project. So:

               

              if window_sum(sum([Minutes]))<=60 then 1 else 0 end

               

              must have the partitioning set to compute using name with ID on the level of detail (this is confusing nomenclature to me, as what it means is ignore Name when calculating the SLA level, but as we have ID on the level of detail, break it down by this)met sla.png

               

              Now for each person we need to sum up the number of SLAs they met. So this is reversed - the ID is irrelevant, sum up for each name, so choose the Total SLA on the drop down:

              name.png

               

              The same holds true for each ID worked on - I want it by name, so set the partitioning on the Total IDs to the same. Then the percent of SLAs in must have each of the three drop downs set in the same way as they are above.

               

              EDIT: new file, at 10:14EST

              • 4. Re: Mental block: count of items with an aggregated measure?
                Ian Waring

                Beautifully explained. Thank you.

                • 5. Re: Mental block: count of items with an aggregated measure?
                  Ian Waring

                  Hiya,

                   

                  Just had a spanner thrown in the works. Each task record has a field that denotes whether an outside event has occurred (like - needed to wait for vendor to supply technical information, waiting for end user to answer a question). Where this happens, the event is written as a string into *every* single record associated with a TASK before I read things into Tableau (system does this automatically). So the table now looks like:

                   

                  Task# Person Minutes Exception

                  134 Joe 60 "BAU" (ie: Business as Usual)

                  134 Ian 95 "BAU"

                  134 Fred 22 "BAU"

                  135 Joe 12 "BAU"

                  136 Ian 22 "BAU"

                  137 Fred 55 "EX"

                  137 Joe 10 "EX"

                  So, task #137 now becomes SLA achieved where it didn't before under the time only rule.

                   

                  I'm now being asked to define "in SLA" as either total time all task steps worked on being less than 1 hour, *or* an exception code present. So "In SLA" becomes a window_sum(sum([Minutes]))<=60 OR (brain hurts here) then 1 else 0

                   

                  Any ideas how I replace "(brain hurts here)" with a function that looks at one or any of the component records to check for the existence of the first two letters of a field containing "EX" (ie: exception)? Been hacking at it all morning, and not getting anywhere fast from an accuracy perspective.

                  • 6. Re: Mental block: count of items with an aggregated measure?
                    Alex Kerin

                    A simple way to do it would be to have a calculation like

                     

                    if [Exception]="Ex" then 0 else [minutes] end

                     

                    And check against this in the [In SLA] calculation instead

                    1 of 1 people found this helpful
                    • 7. Re: Mental block: count of items with an aggregated measure?
                      Ian Waring

                      Alex,

                       

                      May your middle name be Woz (and that's the highest compliment). Engineering simplicity par excellence. Thank you.

                       

                      Ian W.