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.
sum.twbx.zip 12.9 KB
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...
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
sum AK.twbx.zip 20.3 KB
Beautifully explained. Thank you.
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.
1 of 1 people found this helpful
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
May your middle name be Woz (and that's the highest compliment). Engineering simplicity par excellence. Thank you.