6 Replies Latest reply on Mar 24, 2017 5:48 AM by Simon Runc

    How to count number of data which has larger value than average value.

    jessica banna

      Hi,

      I'm new with tableau and need to perform a calculation.

       

      Below is the sample of my data.

      Order_idActual Delivered date
      Estimated Delivered Date
      ord12017-01-032017-01-03

      ord2

      2017-01-112017-01-10
      ord32017-01-072017-01-03
      ord42017-01-152017-01-19
      ord52017-01-132017-01-10
      ord62017-01-112017-01-09
      ord72017-01-202017-01-30

       

      I need some help to do the calculation for count of orders which are late and the date difference between actual delivered date and estimated delivered date is bigger than the average of late days.

       

      So The definition of late orders is when an order has actual delivered date > estimated delivered date. From the table above, I could conclude that ord2, ord3, ord5 and ord 6 are late orders.

      while the others are either on time or early delivery.

       

      ord2 is late by 1 days (date diff between 2017-01-11 and 2017-01-10)

      ord3 is late by 4 days

      ord5 is late by 3 days

      ord6 is late by 2 days

       

      The late average can be calculated and the result will be 2.5

      Then I need to count the order id which is late more than 2.5 days, which is 2 orders (ord4 and ord5)

       

      I have no problem in calculating the date diff, the formula is

      IF ([actual delivery date]>[estimated delivery date]) THEN DATEDIFF("day",[estimated delivery date],[actual delivery date]) ELSE 0 END

       

      Then I could calculate the average by aggregating from the date diff formula. But how to count the number of order?

      Any idea on how to do this calculation?

       

      Thanks,

      Jessica