3 Replies Latest reply on Sep 22, 2016 11:32 AM by Santiago Sanchez

Trimmed Mean/Average with DATEDIFF

I have a workbook in which I have a ton of data that I'm analyzing for a hotel.

I have a date of arrival and a date of booking - the difference in these days is what we call the "booking window".

I've got the datediff working correctly, but would love to trim the top 10% and the bottom 10% off in order to get a realistic view. The reason being is because we've had some people book in a year in advance, then some that book same day. If there were two reservations made, one 365 days in advance and one the same day, the average would be 183 days (where no one booked).

These are causing skews to the data and making our assumptions in correct. If I could use the 80-90% in the middle to take an average, I think we could win.

Thanks!

• 1. Re: Trimmed Mean/Average with DATEDIFF

Interesting challenge, Stephan! Not an statistical expert here, but perhaps you could use percentiles? You can create a calculated field similar to this one:

{ FIXED [Reservation ID] : AVG([DateDiff]) } >= { FIXED : PERCENTILE([DateDiff], 0.1) }

AND

{ FIXED [Reservation ID] : AVG([DateDiff]) } <= { FIXED : PERCENTILE([DateDiff], 0.9) }

We are using LOD expressions here. On the left we calculate the datediff for each reservation. On the right, we identify the top and bottom percentiles. You can then use this as a filter.

This will trim your data, but not exactly sure if it'll do so in the way you're thinking.

• 2. Re: Trimmed Mean/Average with DATEDIFF

Thanks - that is what I thought and was trying to do....

How would I define both of the dates? [ENTRY DATE] & [ARRIVAL DATE]?

• 3. Re: Trimmed Mean/Average with DATEDIFF

Ah, sorry about not being clear on what [DateDiff] represented. It's just another calculated field with a formula like this: DATEDIFF('day', [ENTRY DATE], [ARRIVAL DATE])