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.

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

• 1. Re: How to count number of data which has larger value than average value.

The trick is to make your average a row level value which can be done via an LOD expression:

{ fixed [Dimension 1], [Dimension 2], ...  :  avg([DateDiff Calc]) }

Then you just compare the actual date diff value to the LOD average data diff value at row level.

• 2. Re: How to count number of data which has larger value than average value.

Exactly as John said!...Although before seeing his response I did work up an example (with the same logic as he suggests)...I've also removed the 2 orders which were delivered before the estimated date!...not sure how you want to handle those

Let me know if anything doesn't make sense.

• 3. Re: How to count number of data which has larger value than average value.

Hi Jessica & gentlemen

In addition...was able start but not finalize it with the count of late orders. Can't get my head around it;) Find attached workbook in version 9.3 located in the original thread

Regards,

Norbert

• 4. Re: How to count number of data which has larger value than average value.

Can't get my head around it;)

...I think it's just that you have the DATEDIFF Actual and Estimate the wrong way round

datediff('day',[Actual Delivered Date],[Estimated Deliverd date])

...having said that, there are still 2 orders which were delivered before the estimated date...I just excluded these! (note for Jessica...as I've used FIXED LoDs to calculate the "overall average lateness" I had to make the filter which excludes the 2 negative delta orders, "context" so they wouldn't get included in the average. FIXED LoDs are calculated before any "regular" filters are applied).

• 5. Re: How to count number of data which has larger value than average value.

Hi Simon,

Oke that's the part which i "understand";) but how to count the "late orders above average". It should be 2 is really pulling my very view hairs out of my head....

Regards,

Norbert

• 6. Re: How to count number of data which has larger value than average value.

I have similar hair issues, for similar reasons!! (years of frustrating formulas not doing what I want!!)

So I changed your delta to

datediff('day',[Estimated Deliverd date],[Actual Delivered Date])

which gave the answer of 2...

But I then changed your [late] to if [Delta]>0 then 1 END

and it gives us 4...which I think is correct.

Attached in 10.0