Actual average date interval based on user selection

[qw_jakub]

Hello,

suppose I have a table with a Start and End date.

I have a calendar table interval-matched to these two dates.

The user chooses a month -> all intervals which intersect with this month are filtered in.

I need to calculate the average interval length on these simple rules:

• if the End of the interval is smaller than the biggest date selected by the user (the end of the month) - take the actual day difference: End - Start
• if the interval did not yet come to the end (last day of the month selected by the user < End) - take the difference between the maximal selected day  and the Start : Last Day of the selected Month - Start

I can't seem to figure it out how to compare the End of each row with the Maximum selected day.

For example this:

=avg(if(max(Date) >= End, End - Start, max(Date) - Start))

does not work.

Bellow is an example for how it should work. The same example is in the attached files.

Thank is advance for any helpful comments.

Start	End		User Selects January 2013
1.1.2013	15.2.2013		30
10.1.2013	15.2.2013		21
1.1.2013	15.1.2013		14
1.1.2013	28.2.2013		30
1.2.2013	15.2.2013
1.1.2013	15.1.2013		14
15.1.2013	31.1.2013		16
15.2.2013	28.2.2013
15.1.2013	15.2.2013		16
25.1.2013	15.2.2013		6
		Avg =	18,375

[tresesco]

May be like attached sample?

Update:

For total average work properly, tweak the expression a bit, put avg() around the existing expression like:

=Avg(Aggr(avg(if(max(total Date) >= End,  End - Start, max(total Date) - Start)),Dimension) )

[er_mohit · Accepted solution]

Try this

avg(if(max(TOTAL Date)>=End,(End-Start),max(TOTAL Date) - Start))

[qw_jakub]

Thank you both for helpful answers!

I have based my solution on Mohit's solution. The TOTAL keyword was the key missing in my previous attempts.

I had to solve one more problem: because I did the average difference on two date fields which I also used in an intervalmatch without generating a synthetic key, my expression was calculated only on these distinct combination of dates from the interval table.

I have solved it by generating copies of the two date fields in the main table, which I used for the  calculations.