4 Replies Latest reply on Jun 18, 2012 7:11 AM by Ian Waring

# Window Gymnastics :-)

I have a table of SLA measures for tasks in a Sales Quoting Team that's structured like this:

+----------------------------------------------------------------------------------...

VendorA---------------------------------------+VendorB----------------------------+...

----------------------------------------------|-----------------------------------|...

TaskID   1131  1132  1132  1133  1134   1135  |                                   |

Type     Quote Quote Quote Order Advice Quote |    (Same Structure as VendorA)    |

Minutes  32    17    45    55    65     12    |                                   |

+---------------------------------------------+-----------------------------------+...

The basic rules are that:

1) Each TaskID has one activity type only, although there may be several TASK steps (as in 1132 above)

2) I need to sum the minutes for each of the unique TASK IDs for each vendor

3) Each Type of TASK has an SLA measure (eg: Quotes are in SLA if all steps done in 60 minutes total)

4) Comparison of (2) and (3) tells me how many TASKIDs were in SLA

The transformation i'm trying to end up with is the above to:

+VendorA-----------------------------------------------+VendorB-----

b) #Unique TASKIDs that met SLA                        | (Same for VendorB)

c) Subtract Met SLA from Total to give Missed SLA      |

d) Calculate Percentage Met SLA divided by Unique Tasks|

-------------------------------------------------------+------------

These then get thrown up onto a graph

So the above table would yield (for Vendor A) Countd(TaskIDs) of 5, Unique Tasks that met SLA of 4 (only TaskID 1132 went over it's SLA measure), Missed = 1 and SLA achieve = 4/5 = 80%.

I have (a), (c) and (d) working correctly. However, (b) is quite a nightmare. Started off marking TASKS with a 1 for in SLA or 0 for outside. I can do a window_sum to correctly mark each TaskID with the total minutes that TASK is summed to consume in that pane (as a Task Type only maps to one SLA measure, I just do a MIN to aggregate the SLA time allowed on that side of the comparison), but if I count the result, TaskID 1132 gets counted twice (as there are two instances - two substeps in the same TASK - of it).

So, I try to have the SLA comparison spin out the TaskID if it's in SLA, or a null if not, and then do a Window_Sum(COUNTD()) to give the correct number of unique TaskIDs that achieve SLA for that vendor. However, Tableau then disallows entry of the calculation, on the grounds that it can't handle IF statements in a Calculated Field (which I need to assess whether the TASK is in SLA or not) that can throw nulls.

Any ideas how to squeeze logic in and report the correct numbers?

• ###### 1. Re: Window Gymnastics :-)

Hi Ian,

This is a probable an over simplification, but if you can produce (a) and (c) why can't you just do the subtraction:

(a)-(c)=(b)

doesn't it?

--Shawn

• ###### 2. Re: Window Gymnastics :-)

Recursion, see Recursion :-)

I need to work out how many tasks meet SLA in (b), or how many tasks fail SLA in (c). In either case, I need to de-dupe the task IDs somehow. My original attempt was to list TASK IDs that made SLA, then to Countd them. However, if a Task fails SLA, I don't want the Task ID in there at all (or anything that will get counted), but Tableau is refusing to let me throw NULLs for the Tasks that fail SLA. People's pay is dependent on the result, so I need to work out how to do this accurately. More experiments at work tomorrow.

• ###### 3. Re: Window Gymnastics :-)

(side note, I have no idea how recursion would play a role in this)

• ###### 4. Re: Window Gymnastics :-)

Brilliant!!!!!

Windows_sum(Case Of...) has my brain spinning though :-)

Really excellent. Thank you, Joe/