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

# Mental block: count of items with an aggregated measure?

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.

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)

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:

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?

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?

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?

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)

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:

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?

Beautifully explained. Thank you.

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

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:

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?

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?

Alex,

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

Ian W.