Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SUM + IF condition

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

1 Reply
swuehl
MVP
MVP

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.