Tableau Prep Help

1. To answer this question in Tableau Desktop, we'll use the DATEDIFF function. This function takes three arguments—the date part, the start date, and the end date. Since we want to know the days between these events, we'll use the date part 'day'. Our start and end dates are in the data set as 1st Infraction Date and 2nd Infraction Date.

2. The calculation is:

   Time Between Infractions = DATEDIFF('day', [1st Infraction Date], [2nd Infraction Date])

3. We can plot that against Driver ID as a bar chart. Note that seven drivers had no second infraction, so there are seven nulls.

   

1. To answer this question in Tableau Desktop, we'll create a scatter plot of 1st Fine Amount and 2nd Fine Amount. By bringing Driver ID to the Detail shelf on the Marks card, we can create a mark for each driver.

2. To add a trend line, use the Analytics tab in the left-hand pane and bring out a linear trend line. Hovering over the trend line, we can see the R-squared value is practically zero, and the p-value is far above any cutoff for significance. We can determine that there is no correlation between first and second fine amount.

   If we were to use this scatter plot in a dashboard, the trend line should be removed.

When we want to go deeper in our analysis, we may need to create some calculations.

1. To answer this in Tableau Desktop, we need to add the fines for both infractions into a single field. Because some drivers may not have had a second infraction, we need to use the zero null ZN function to turn any nulls for 2nd Fine Amount into zeros. Failing to do this will result in nulls if there isn't a second fine.

2. The calculation is:

   Total Amount Paid = [1st Fine Amount] + ZN([2nd Fine Amount])

3. We can plot Total Amount Paid against Driver ID and sort the bar chart.

1. To answer this in Tableau Desktop, we need to do a fancier IF calculation, comparing if the first and second infraction types are the same. If they are, we want to assign the value "1". If they are not the same, we'll assign "2". Since we only care about multiple infraction types, any other result, such as a null second infraction type, will be assigned "1".

2. The calculation is:

   Number of Infraction Types =

   IF [1st Infraction Type]=[2nd Infraction Type] THEN 1 
   ELSEIF [1st Infraction Type]!= [2nd Infraction Type] THEN 2 
   ELSE 1 END

3. We can then plot Number of Infraction Types against Driver ID and sort the bar chart.

1. To answer this in Tableau Desktop, we cannot simply divide the total fine amount by two, since some drivers only had one infraction. We also can't calculate the average fine per driver and take the average of those values, because averaging averages can lead to inconsistencies. Instead, we need to calculate the total amount paid by drivers who never attended traffic school, then divide by the total number of infractions associated with those fines.

   1. First, we need to determine if each driver had a second infraction. We can leverage the fact the information in all the "2nd" fields will be null if there was no second infraction and start building the calculation:

      IFNULL([2nd Infraction Type], 'no')

      This will return an infraction type if it exists, or "no" if there was no second infraction.

   2. Next, we need to turn this information into the number of infractions, 1 or 2. If the result of our IFNULL calculation is "no", then the driver should be marked as having one fine. Any other result should be marked as having two fines. The calculation is:

      Number of Infractions =

      IF IFNULL([2nd Infraction Type], 'no') = 'no' THEN 1 
      ELSE 2 
      END

   3. Now we need to consider the total fine amount. Similarly to question 3 above, we'll add the first and second fine amounts, with a ZN function around the second. However, because we want this to be computed at the level of the entire data set, it's a best practice to specify the aggregations, SUM, in the calculation itself. The calculation is: 

      SUM([1st Fine Amount]) + SUM( ZN([2nd Fine Amount]) )

   4. To bring it all together, we'll take this total fine amount and divide it by our new Number of Infractions calculated field to determine the average fine amount:

      Average Fine = ( SUM([1st Fine Amount]) + SUM( ZN([2nd Fine Amount]) ) ) / SUM([Number of Infractions])

2. We also need to filter out drivers who ever attended traffic school—but that information is also stored across two fields.

   1. Tableau is very efficient at numerical calculations. We'll phrase this with numbers to help performance as much as we can. To combine these two fields, we'll create a calculation for each one that says "Yes = 1" and "No = 0" (null should also = 0, for drivers with no second infraction). By summing the outcome of these calculations, any driver with an overall value of 0 never went to traffic school (and a value of 1 or 2 represents how many times they went). We can then filter to keep only drivers with a value of 0.

   2. This time, we'll use a CASE statement instead of IF. They function very similarly but have different syntax. The start of the calculation should look like this:

      CASE [1st Traffic School]
      WHEN 'Yes' THEN 1
      WHEN 'No' THEN 
      ELSE 0
      END

   3. And then we'll do the same thing for 2nd Traffic School. We can add both pieces in the same calculation by wrapping each case statement in parentheses and adding a plus between them. Removing some of the line breaks, it looks like this:

      Number of Traffic School Attendances =

      (CASE [1st Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)
      +
      (CASE [2nd Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)
      

   4. If we drag Number of Traffic School Attendances to the Dimensions area of the Data pane (above the line), the values 0–2 will become discrete.

   5. Now if we filter on Number of Traffic School Attendances, we can select just the 0 and know we're getting drivers who have never attended traffic school.

3. To answer the original question, we'll simply bring Average Fine to the Textshelf on the Marks card.

   Because we built the aggregations into the calculation, the aggregation on the pill will be AGG and we cannot change it. This is as expected.

1. To answer this in Tableau Desktop, as we did with the first data set, we'll use the DATEDIFF function. This function requires a start date and an end date. This information is present in our data, but all in one field. We need to pull it out into two fields.

   1. Create two preliminary calculated fields:

      1st Infraction Date = IF [Infraction Number] = "1st" THEN [Infraction Date] END

      2nd Infraction Date = IF [Infraction Number] = "2nd" THEN [Infraction Date] END

   2. Because we want to make sure both of these values are available to be compared for each driver, we need to fix them to the level of Driver ID.

      Note: Don't believe me? Try to do a DATEDIFF calculation with these two fields as they are: Time Between Infractions = DATEDIFF('day', [1st Infraction Date], [2nd Infraction Date])
      You'll get null results everywhere, because Tableau is trying to compare across a data structure that looks like this:
      
      Here, the row that knows what the first date is doesn't know what the second date is, and vice versa. To get around this, we'll use a FIXED Level of Detail expression to force these first and second dates to be related by Driver ID.

   Edit each calculation as follows:

   1st Infraction Date = { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "1st" THEN [Infraction Date] END ) }

   2nd Infraction Date = { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "2nd" THEN [Infraction Date] END ) }

   Note: The original IF calculation must be aggregated when embedded in an LOD expression. We can use any basic aggregation that will preserve the date value (so aggregations like SUM, AVG, or MIN work, but not CNT or CNTD).

   Note: These calculations can also be created in Tableau Prep Builder. For more information on LOD expressions in Prep, see Create Level of Detail, Rank, and Tile Calculations .

   3. Now we can create the DATEDIFF calculation as follows:

      Time Between Infractions = DATEDIFF('day', [1st Infraction Date], [2nd Infraction Date])

      • If we want to look at weeks or months, simply modify the date part (currently 'day').

      • It would also be possible to create a single calculation for the entire thing by placing the FIXED calcs inside the DATEDIFF directly: 

        DATEDIFF ( 'day', 
        { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "1st" THEN [Infraction Date] END ) },
        { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "2nd" THEN [Infraction Date] END ) }
        )

   4. Plot Time Between Infractions on Columns and Driver ID on Rows.

The results will be identical to the outcome with the unpivoted data structure.

1. To answer this in Tableau Desktop, we'll use very similar logic to the previous question. We'll use Infraction Number to identify if a given row is the first or second infraction, then pull out the fine amount accordingly.

   1. If all we want to do is make a scatter plot, we can skip the LOD portion and just use the IF calculation:

      1st Fine Amount = IF [Infraction Number] = "1st" THEN [Fine Amount] END

      2nd Fine Amount = IF [Infraction Number] = "2nd" THEN [Fine Amount] END

   2. However, if we wanted to compare and see the difference in amount between the first and second fines for a single driver, we'd run into the same null issue as with the dates. It can't hurt to wrap these calculations in a FIXED LOD, so it might be good just to do so from the start:

      1st Fine Amount = { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "1st" THEN [Fine Amount] END ) }

      2nd Fine Amount = { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "2nd" THEN [Fine Amount] END ) }

      These calculations can also be created in Tableau Prep Builder. For more information on LOD expressions in Prep, see Create Level of Detail, Rank, and Tile Calculations .

   3. Create a scatterplot with 1st Fine Amount on Columns and 2nd Fine Amount on Rows and bring out a linear trend line as before.

The results will be identical to the outcome with the unpivoted data structure.

1. To answer this question in Tableau Desktop, the pivoted data structure is ideal. All we need to do is bring out Driver ID and Fine Amount into a bar chart. The default aggregation is already SUM, so the total amount paid by the driver will automatically be plotted.

The results will be identical to the outcome with the unpivoted data structure.

1. To answer this question in Tableau Desktop, the pivoted data structure is ideal. All we need to do is bring out Driver ID and a Count Distinct of Infraction Type as a bar chart, and we'll have our answer.

The results will be identical to the outcome with the unpivoted data structure.

1. To answer this in Tableau Desktop, we cannot simply divide the total fine amount by two, since some drivers only had one infraction. We also can't calculate the average fine per driver and take the average of those values, because averaging averages can lead to inconsistencies. Instead, we need to calculate the total amount paid by drivers who never attended traffic school, then divide by the total number of infractions associated with those fines.

   1. First, we need to determine if each driver had a second infraction. We can leverage the fact 2nd Infraction Date will be null if there was no second infraction and start building the calculation:

      IFNULL(STR([2nd Infraction Date]), 'no')

      This will return the date of the second infraction if it exists, or "no" if there was no second infraction.

      Note: The STR portion of this calculation is necessary because IFNULL needs consistency of data type in its arguments. Because we want to return the string "no" for null values, we need to convert the date to a string as well.

   2. Next, we need to turn this information into the number of infractions, 1 or 2. If the result of our IFNULL calculation is "no", then the driver should be marked as having one fine. Any other result should be marked as having two fines. The calculation is:

      Number of Infractions =

      IF IFNULL(STR([2nd Infraction Date]), 'no')= 'no' THEN 1 
      ELSE 2 
      END
      

   3. Now we need to consider the average fine amount. We already have a single field for Fine Amount. All we need to do is divide that by our new Number of Infractions field, wrapping both in SUM: 

      Average Fine = (SUM([Fine Amount]) / SUM([Number of Infractions])

2. We also need to filter out drivers who attended traffic school. It looks like we could use the Traffic School field and filter on Traffic School = no. However, this would filter on infractions not associated with traffic school, not drivers who never went to traffic school. If a driver went to traffic school for one infraction but not the other, we don't want either infraction to be considered here—that driver has been to traffic school and therefore doesn't fit the parameters of the question.

   What we want to do is filter out any driver who attended traffic school. In terms of the data, we want to filter out any driver who has a "Yes" for Traffic School on any row, regardless of which infraction it's associated with. Let's build our calculation in stages, using a simple view to help keep track of what's happening:

   1. First, we want to know if a driver has a "Yes" for Traffic School. Drag Driver ID to Rows and Traffic School to Columns. We'll get a text table with placeholder "Abc" text indicating the relevant values for each driver.

   2. Next, we want to build a calculation that will identify if the value of Traffic School is "Yes". The first stage of the calculation is:

      Attended Traffic School = CONTAINS([Traffic School), 'Yes')

      If we bring Attended Traffic School to the Color shelf on the Marks card, we see it accurately labels "False" for every mark in the "No" column, and "True" for every mark in the "Yes" column.

      

   3. However, what we really want is this information at the level of the driver, not the infraction. An LOD expression is a natural fit when trying to compute a result at a different level of detail than the basic structure of the data. We'll make this a FIXED LOD expression. But, as we know, the aggregate expression portion of an LOD must be aggregated. Previously, we've used MIN. Will that work here? We'll modify the calculation to be: 

      Attended Traffic School = { FIXED [Driver ID] : MIN( CONTAINS([Traffic School], 'Yes'))}

      

      With that change applied in the view, we see the opposite of what we want. Any driver that has a "No" is marked as "False" across the board. Instead, we want to carry the "Yes" as a "True" for every record for that driver. What is MIN doing here? It's picking the first response alphabetically, that is, "No".

   4. What if we changed it to MAX? Would that take the last response alphabetically? We'll modify the calculation to be: 

      Attended Traffic School = { FIXED [Driver ID] : MAX ( CONTAINS( [Traffic School], 'Yes') ) }

      

      And here we have it: if a driver has "Yes" anywhere in the data, they are marked as "True" for having attended traffic school, even on the infraction that didn't involve traffic school.

   5. If we bring Attended Traffic School to the Filter shelf and select only "False", we'll be left with only drivers who never attended traffic school.

3. To answer the original question, with our filter in place we'll simply bring Average Fine to the Textshelf on the Marks card. Because we built the aggregations into the calculation, the aggregation on the field will be AGG and we cannot change it. This is as expected.

The results will be identical to the outcome with the unpivoted data structure.

1. To answer this in Tableau Desktop, we'll again use the DATEDIFF function. This function requires a start date and an end date. This information is present in our data, but all in one field. We need to pull it out into two fields. Because we want to make sure both of these values are available to be compared for each driver, we need to fix them to the level of Driver ID.

   1. To find the first infraction date, we use the calculation:

      1st Infraction = { FIXED [Driver ID] : MIN ( [Infraction Date] ) } 

   2. We'll do the second infraction date in stages.

      1. To start, we need to look at just the dates that are larger than the first date:

         IF [Infraction Date] > [1st Infraction] THEN [Infraction Date] END

      2. But this will give us every infraction after the first, and we only want the second. So we want the smallest of these dates. Wrap the whole thing in MIN:

         MIN( IF [Infraction] : [1st Infraction] THEN [Infraction Date] END )

      3. We also want to recalculate the second infraction date for each driver. That's where LOD expressions come in. We'll fix this to the level of Driver ID:

         2nd Infraction = { FIXED [Driver ID] : MIN ( IF [Infraction Date] > [1st Infraction] THEN [Infraction Date] END ) }

   3. And we can now create the DATEDIFF calculation:

      Time Between Infractions = DATEDIFF('day', [1st Infraction], [2nd Infraction])

The results will be identical to the outcomes with the other two data structures.

1. To answer this in Tableau Desktop, we'll use similar logic to the pivoted data version of this question. We'll use the 1st Infraction and 2nd Infraction fields we created for question I to identify if a given row is the first or second infraction, then pull out the fine amount accordingly.

   1. If all we want to do is make a scatter plot, we can skip the LOD portion and just use an IF calculation:

      1st Fine Amount = IF [1st Infraction] = [Infraction Date] THEN [Fine Amount] END

      2nd Fine Amount = IF [2nd Infraction] = [Infraction Date] THEN [Fine Amount] END

   2. However, if we want to compare and see the difference in amount between the first and second fines for a single driver, we'd run into issues with nulls, as in the first data structure. It can't hurt to wrap these calculations in a FIXED LOD, so it might be good just to do so from the start:

      1st Fine Amount = { FIXED [Driver ID] : MIN ( IF [1st Infraction] = [Infraction Date] THEN [Fine Amount] END ) }

      2nd Fine Amount = {FIXED [Driver ID] : MIN( IF [2ndInfraction] = [Infraction Date] THEN [Fine Amount] END ) }

The results will be identical to the outcomes with the other two data structures.

1. To answer this in Tableau Desktop, we need to first realize something about the LOD-only method. Both methods using Tableau Prep filter out records that are not the first or second infraction for a driver. The LOD method in Tableau Desktop keeps all records. This means that if we were to create a viz of SUM(Amount Paid) by Driver ID, the Tableau Desktop-only version will show higher amounts for drivers with more than two infractions. To get a Total Amount Paid value from the complete data that matches the other methods, instead of using the original Fine Amount field, we instead need to sum the first and second fines like we did with the first data structure.

2. Using the fields we created for question 2, we'll add the two fine amounts. ZN is necessary to prevent a null result for any drivers who only had one infraction. The calculation is: 

   Total Amount Paid = [1st Fine Amount] + ZN([2nd Fine Amount])

The results will be identical to the outcomes with the other two data structures.

1. To answer this question in Tableau Desktop, we can't simply bring out Driver ID and a Count Distinct of Infraction Type. Because this data set has infractions beyond the second, some drivers may have more than two infraction types. To match the results with the other methods, we need to limit the scope to just the first two infractions.

2. We can to pull out the 1st and 2nd infraction types, wrap them in LOD expressions to make them FIXED to the driver, then use an IF calculation to count the types:

   1. 1st Infraction Type = { FIXED [Driver ID] : MIN ( IF [1st Infraction] = [Infraction Date] THEN [Infraction Type] END ) }

   2. 2nd Infraction Type = { FIXED [Driver ID] : MIN ( IF [2nd Infraction] = [Infraction Date] THEN [Infraction Type] END ) }

   3. Number of Infraction Types =

      IF [1st Infraction Type] = [2nd Infraction Type] THEN 1 
      ELSEIF [1st Infraction Type] != [2nd Infraction Type] THEN 2
      ELSE 1 END 

      Note: It's also possible to create many of these calculations as a single field by nesting the initial calculations directly in the larger calculation. Here, the combined calculation would look like this:
      IF
{FIXED [Driver ID] : MIN(IF [1st Infraction]=[Infraction Date] THEN [Infraction Type] END)}
=
{FIXED [Driver ID] : MIN(IF [2nd Infraction]=[Infraction Date] THEN [Infraction Type] END)}
THEN 1

ELSEIF
{FIXED [Driver ID] : MIN(IF [1st Infraction]=[Infraction Date] THEN [Infraction Type] END)}
!=
{FIXED [Driver ID] : MIN(IF [2nd Infraction]=[Infraction Date] THEN [Infraction Type] END)}
THEN 2

ELSE 1
END
      
      Which is a bit harder to make sense of, but works if preferred. (Note that line breaks and some spaces do not impact how a calculation is interpreted by Tableau.)

3. We can then plot Number of Infraction Types against Driver ID and sort the bar chart.

The results will be identical to the outcomes with the other two data structures.

1. To answer this in Tableau Desktop, we cannot simply divide the total fine amount by two, since some drivers only had one infraction. We also can't calculate the average fine per driver and take the average of those values, because averaging averages can lead to inconsistencies. Instead, we need to calculate the total amount paid by drivers who never attended traffic school, then divide by the total number of infractions associated with those fines.

   1. First, we need to determine if each driver had a second infraction. We can leverage the fact the information in all the "2nd" fields will be null if there was no second infraction and start building the calculation:

      IFNULL([2nd Infraction Type], 'no')

      This will return an infraction type if it exists, or "no" if there was no second infraction.

   2. Next, we need to turn this information into the number of infractions, 1 or 2. If the result of our IFNULL calculation is "no", then the driver should be marked as having one fine. Any other result should be marked as having two fines. The calculation is:

      Number of Infractions =

      IF IFNULL([2nd Infraction Type], 'no') = 'no' THEN 1
      ELSE 2
      END

   3. For the Total Amount Paid, we can use the calculation from question 3. To bring it all together, we'll take this total fine amount and divide it by our new Number of Infractions calculated field to determine the average fine amount:

      Average Fine = SUM([Total Amount Paid]) / SUM([Number of Infractions])

2. We also need to filter out drivers who attended traffic school. Because this data set contains some drivers with a third or fourth infraction, we can't use the same method as the pivoted data structure. Instead, we'll follow the same method as the unpivoted data, summarized here:

   1. First, we need to built two calculations identifying if the first and second infractions involved traffic school or not: 

      1st Traffic School = { FIXED [Driver ID] : MIN (IF [1st Infraction] = [Infraction Date] THEN [Traffic School] END ) }

      2nd Traffic School = { FIXED [Driver ID] : MIN (IF [2nd Infraction] = [Infraction Date] THEN [Traffic School] END ) }

   2. Then we'll add those values to get the overall number of traffic school attendances: 

      Number of Traffic School Attendances =

      (CASE [1st Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)
      +
      (CASE [2nd Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)

   3. If we drag Number of Traffic School Attendances to the Dimensions area of the Data pane, the values 0–2 become discrete.

   4. Now if we filter on Number of Traffic School Attendances, we can select just the 0 and know we're getting drivers who have never attended traffic school.

3. To answer the original question, we'll simply bring Average Fine to the Textshelf on the Marks card. Because we built the aggregations into the calculation, the aggregation on the field will be AGG and we cannot change it. This is as expected.

The results will be identical to the outcomes with the other two data structures.

Driver Infractions

• Time Between Infractions = DATEDIFF('day', [1st Infraction Date], [2nd Infraction Date])

• Total Amount Paid =[1st Fine Amount] + ZN([2nd Fine Amount])

• Number of Infraction Types = IF [1st Infraction Type]=[2nd Infraction Type] THEN 1 ELSEIF [1st Infraction Type]!= [2nd Infraction Type] THEN 2 ELSE 1 END

• Number of Infractions = IF IFNULL([2nd Infraction Type], 'no') = 'no' THEN 1 ELSE 2 END

• Average Fine = ( SUM([1st Fine Amount]) + SUM( ZN([2nd Fine Amount]) ) ) / SUM([Number of Infractions])

• Number of Traffic School Attendances = (CASE [1st Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END) + (CASE [2nd Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)

Pivoted Driver Infractions

• 1st Infraction = {FIXED [Driver ID] : MIN(IF [Infraction Number] = "1st" THEN [Infraction Date] END)}

• 2nd Infraction = {FIXED [Driver ID] : MIN(IF [Infraction Number] = "2nd" THEN [Infraction Date] END)}

• Time Between Infractions = DATEDIFF('day', [1st Infraction], [2nd Infraction])

• 1st Fine Amount = {FIXED [Driver ID] : MIN( IF [Infraction Number] = "1st" THEN [Fine Amount] END ) }

• Number of Infractions = IF IFNULL(STR([2nd Infraction]), 'no')= 'no' THEN 1 ELSE 2 END

• Average Fine = SUM([Fine Amount])/SUM([Number of Infractions])

• Attended Traffic School = { FIXED [Driver ID] : MAX( CONTAINS([Traffic School], 'Yes'))}

LOD Driver Infractions

• 1st Infraction = {FIXED [Driver ID] : MIN([Infraction Date])}

• 2nd Infraction = { FIXED [Driver ID] : MIN( IF [Infraction Date] > [1st Infraction] THEN [Infraction Date] END ) }

• Time Between Infractions = DATEDIFF('day', [1st Infraction], [2nd Infraction])

• 1st Fine Amount = {FIXED [Driver ID] : MIN( IF [1st Infraction] = [Infraction Date] THEN [Fine Amount] END ) }

• 2nd Fine Amount = {FIXED [Driver ID] : MIN( IF [2nd Infraction] = [Infraction Date] THEN [Fine Amount] END ) }

• Total Amount Paid = [1st Fine Amount] + ZN([2nd Fine Amount])

• 1st Infraction Type = {FIXED [Driver ID] : MIN( IF [1st Infraction] = [Infraction Date] THEN [Infraction Type] END ) }

• 2nd Infraction Type = {FIXED [Driver ID] : MIN( IF [2nd Infraction] = [Infraction Date] THEN [Infraction Type] END ) }

• Number of Infraction Types = IF [1st Infraction Type]=[2nd Infraction Type] THEN 1 ELSEIF [1st Infraction Type]!= [2nd Infraction Type] THEN 2 ELSE 1 END

• Number of Infractions = IF IFNULL([2nd Infraction Type], 'no') = 'no' THEN 1 ELSE 2 END

• Average Fine = SUM ([Total Amount Paid]) / SUM([Number of Infractions])

• 1st Traffic School = {FIXED [Driver ID] : MIN (IF [1st Infraction] = [Infraction Date] THEN [Traffic School] END ) }

• 2nd Traffic School = {FIXED [Driver ID] : MIN (IF [2nd Infraction] = [Infraction Date] THEN [Traffic School] END ) }

• Number of Traffic School Attendances = (CASE [1st Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END) + (CASE [2nd Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)