Exclude double entries from calculation

[klaus_feldam]

I would very much appreciate some assistance with the exclusion of double entries from an AVG calculation.

In the example below, I have three records, two of which is are related to the same sales activity. While both records are valid, I only want to count the latest record in my AVG calculation of how many days the activity took to close.

Currently, my AVG calculation comes up with an average of 39 days to close.

It should only be 28.

Any ideas of a quick way of only including the latest sales activity in the calculation?

ID	Sales Area	Sales Person	Start Date	End Date	Days to Close
123	Orlando	Donald Smith	3/19/14	5/19/14	61
234	Los Angeles	Jan Anderson	4/4/14	4/30/14	26
123	Orlando	Donald Smith	4/19/14	5/19/14	30
Avg					39
Avg (without first ID)					28

[Anonymous]

What is the source of this data?

This can be done in SQL (If thats where it comes from) with a window function:

Select

...

Rank() Over(Partition by ID, [Sales Area] Order by [Start Date] desc, [End Date] as RecordOrder

Then, in your application you could add {$<RecordOrder={1}>} to your expressions.

Not sure how best to do this at the application level.

[JonnyPoole · Accepted solution]

Here is a solution that uses the data that you posted above. The expression will work in a chart or text box.

It calculates the avg by summing only 1 record per ID where the start date = max start date for that ID and divides by the number of IDs

expression:

=sum( aggr( if( Max( total <ID> [Start Date]) = [Start Date], [Days to Close]), ID, [Sales Area],[Sales Person],[Start Date],[End Date]))

/

count( DISTINCT ID)

[ali_hijazi]

I would do the following in the script

I first sort data by ID and date desc

and then I reload again saying:

if(RowNO() = 1 or ID <> previous(ID), 1,peek(rank)+1) as rank

then in the expression I would write sum({<rank={1}>} days_to_close)

hope this helps

[klaus_feldam]

Thanks, Jonathan.

Extremely helpful.

Question:

[Days to Close] is a calculation of [Start Date] - [End Date]. I wonder how a revised would formula look.

Like this?

=sum( aggr( if( Max( total <ID> [Start Date]) = [Start Date], ([Start Date] - [End Date])), ID, [Sales Area],[Sales Person],[Start Date],[End Date]))

/

count( DISTINCT ID)

[JonnyPoole]

That should work too.  You could also use the INTERVAL function to format the interval in Days, hours, minutes etc...

Did it work ?

[klaus_feldam]

Yes, it worked well.

Thank you very much, Jonathan.