SUM + IF condition

[#1]

Hello All Comm,

I have a big problem in here that is taking me upside down...hope some one can help me:

I need to calculate the % of progress of a country with 12 sites (each site has its own progress) therefore the % Progress of the country would be

     sum(%Country)/12

But when I add the AnnotationDate "factor" is when I have the problem, cause each site can have different AnnotationDates but I only want to take:

     " The last AnnotationDate of each Site"

To make the %Progress of the Country

Does it make sense?

Many thanks in advance,

B.

[swuehl · Accepted solution]

You can try something like

=avg( aggr( FirstSortedValue(DISTINCT  [1. PRELIMINARES], -[FECHA DE ANOTACIÓN]), PAIS, SITE_ID))

Your records show multiple entries per site and date (e.g. Site AR01 2 entries on 29/04/2013), so you need to decide how to handle this (when talking about the last annotation date per site). You can try using DISTINCT qualifier with the FirstSortedValue function or maybe using the RecID I introduced in the LOAD script.

Please check attached sample.