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

    Window Gymnastics :-)

    Ian Waring

      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-----

      a) countd(TaskID)                                      |

      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?