2 Replies Latest reply on Aug 21, 2012 12:17 PM by Eric Wilding

    Not able to calculate totals using IF...THEN...ELSE

    Eric Wilding

      I am trying to create the table below that shows distinct counts of person ID's by course delivery methods, excluding certain subjects. However, I cannot get the totals as shown below.

       

      Delivery Distinct Count of ID's
      W Delivery without AVSC Subject526
      W Delivery with AVSC Subject9
      Total W Delivery Code (regardless of subject)530
      V Delivery Code without AVSC Subject149
      V Delivery Code with AVSC Subject47
      Total V Delivery Code (regardless of subject)192

       

      I am using the following IF...THEN...ELSE to calculate the delivery variables.

       

      IF [DELIVERY_CODE] = 'W' AND [SUBJECT] <> 'AVSC' THEN 'W - without AVSC'

      ELSEIF [DELIVERY_CODE] = 'W' AND [SUBJECT] = 'AVSC' THEN 'W - with AVSC'

      ELSEIF [DELIVERY_CODE] = 'W' THEN 'W'

      ELSEIF [DELIVERY_CODE] = 'V' AND [SUBJECT] <> 'AVSC' THEN 'V - without AVSC'

      ELSEIF [DELIVERY_CODE] = 'V' AND [SUBJECT] = 'AVSC' THEN 'V - with AVSC'

      ELSEIF [DELIVERY_CODE] = 'V' THEN 'V'

      ELSE 'Other'

      END

       

      ...And below are the results...Which matches the above but there are no totals. How on earth do I do this? In my example twbx I have greatly simplified my data into just three delivery types (W, T, V) and six subjects, thus making the data set small and manageable. I needs to see W delivery type courses with and without the AVSC subject AND also total W delivery type courses regardless of any of the available subjects. The same with V delivery types.

       

      Calculation1    Distinct count of ID

      W - without AVSC    526

      W - with AVSC    9

      V - without AVSC    149

      V - with AVSC    47

      Other    104

       

      Thanks

      Eric