Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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.

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: SUM + IF condition

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.

1 Reply
MVP
MVP

Re: SUM + IF condition

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.

Community Browser