Aggr() dans Qlik Sense : la table virtuelle et ses quatre pièges

Quelle est la vente moyenne par client ? Combien de clients dépassent 1 000 € ? Qui est le meilleur client du mois ? Ces questions demandent deux niveaux de calcul : d’abord un total par client, puis une moyenne, un comptage ou un maximum sur ces totaux. C’est le rôle d’Aggr(), l’une des fonctions les plus puissantes des expressions Qlik, et l’une des plus mal comprises.

Cet article fait partie de notre série sur les fonctions et mots-clés méconnus de Qlik Sense. Il s’appuie sur l’aide Qlik et sur le billet « Pitfalls of the Aggr function » d’Henric Cronström ; les liens sont en fin d’article.

Une table virtuelle

Aggr(Sum(Sales), Customer) calcule Sum(Sales) pour chaque client et range les résultats dans une table virtuelle, invisible, d’une ligne par client. Une seconde fonction, l’agrégation externe, réduit ensuite cette table à une valeur.

À gauche, sept lignes de ventes pour les clients A, B et C. Une flèche mène à la table virtuelle Aggr(Sum(Sales), Customer), qui compte trois lignes : A 1200, B 450, C 1050. Une seconde flèche mène à l’agrégation externe Avg, qui donne 900. En dessous : Avg(Sales) vaut 385,7, la moyenne des sept lignes, alors que Avg(Aggr(Sum(Sales), Customer)) vaut 900, la moyenne des trois clients.

Deux moyennes, deux questions différentes.

Avg(Aggr(Sum(Sales), Customer))   // vente moyenne par client : 900
Avg(Sales)                        // vente moyenne par ligne : 385,7

La syntaxe complète :

Aggr({set} [DISTINCT | NODISTINCT] expression, dimension1 [, dimension2 …])

Chaque dimension doit être un champ : une expression n’est pas acceptée. Une dimension peut porter un ordre de tri, nous y revenons plus bas.

Les quatre pièges

Dans « Pitfalls of the Aggr function » (2015), Henric Cronström en relève quatre. Ils expliquent l’essentiel des résultats inattendus.

1. Oublier l’agrégation interne. Le premier argument est une mesure : il lui faut une fonction d’agrégation. Aggr(Sales, Customer) est traité comme Aggr(Only(Sales), Customer), qui renvoie NULL dès qu’un client a plusieurs ventes différentes.

2. Oublier l’agrégation externe. Aggr() renvoie plusieurs valeurs. Sans fonction autour, Qlik applique Only(), et la mesure vaut NULL dès qu’il y a plus d’une valeur, en particulier sur la ligne de total. Écrivez toujours Sum(Aggr(…)), Avg(Aggr(…)) ou Max(Aggr(…)).

3. Mettre le set analysis à un seul niveau. Les sélections s’appliquent aux deux niveaux, mais un set analysis ne s’applique que là où il est écrit. Pour calculer sur le seul produit P1, répétez-le :

Avg({<Product = {'P1'}>} Aggr({<Product = {'P1'}>} Sum(Sales), Customer))

Avec le set seulement à l’extérieur, la table virtuelle est calculée sans le filtre.

4. Choisir un grain trop grossier. Les dimensions d’Aggr() doivent être les mêmes que celles du graphique, ou plus fines. Un Aggr() par mois fonctionne dans un graphique par année. L’inverse ne fonctionne pas : la valeur de l’année ne s’affiche que sur un mois, et les autres valent 0.

Deux tables. À gauche, un graphique par Year avec Sum(Aggr(Sum(Sales), Year, Month)) : l’année 2025 reçoit 2700, la somme de ses mois. À droite, un graphique par Month avec Sum(Aggr(Sum(Sales), Year)) : le mois 1 reçoit 2700, les mois 2 et 3 valent 0.

Un grain plus fin que le graphique fonctionne ; un grain plus grossier, non.

Pour afficher le total du client sur chaque ligne d’une table client × produit, deux solutions : Sum(TOTAL <Customer> Sales), la plus simple, ou Aggr(NODISTINCT Sum(Sales), Customer). Le qualificatif NODISTINCT renvoie la valeur sur chaque ligne de la combinaison, au lieu d’une seule.

Ce qu’Aggr permet

Compter et filtrer sur un résultat intermédiaire. Les clients qui dépassent 1 000 € :

Sum(Aggr(If(Sum(Sales) > 1000, 1, 0), Customer))

Une recherche par expression en set analysis donne le même résultat, souvent plus vite : Count({<Customer = {"=Sum(Sales) > 1000"}>} DISTINCT Customer). Elle fait l’objet de notre article sur le set analysis avancé.

Créer une dimension calculée. Une dimension qui ne montre que les deux meilleurs clients :

=Aggr(If(Rank(Sum(Sales)) <= 2, Customer), Customer)

Décochez Inclure les valeurs nulles pour masquer les autres.

Cumuler dans un ordre maîtrisé. Une dimension d’Aggr() peut porter un tri. C’est ce qui rend fiable un cumul calculé avec les fonctions inter-lignes, quel que soit le tri du graphique :

Sum(Aggr(RangeSum(Above(Sum(Sales), 0, 12)), (Month, (NUMERIC, ASCENDING))))

Les types de tri sont NUMERIC, TEXT, FREQUENCY et LOAD_ORDER, avec ASCENDING ou DESCENDING. L’aide Qlik précise que, sans tri, la même expression ne cumule pas correctement.

Le coût

Chaque évaluation d’Aggr() construit sa table virtuelle. Sur une grosse table, avec plusieurs dimensions, c’est coûteux, et le calcul se refait à chaque sélection. Si le résultat intermédiaire ne dépend pas des sélections, comme le total historique d’un client, calculez-le plutôt dans le script.

À retenir

Deux niveaux de calcul. Aggr construit une table virtuelle, une agrégation externe la réduit.
Une agrégation dedans, une dehors. Sinon, Qlik applique Only() et renvoie NULL.
Le set analysis aux deux niveaux. Un filtre écrit à l’extérieur ne filtre pas la table virtuelle.
Un grain égal ou plus fin. Les dimensions d’Aggr doivent être celles du graphique, ou plus détaillées.
Un tri pour les cumuls. (Month, (NUMERIC, ASCENDING)) fixe l’ordre de la table virtuelle.
Le script quand c’est possible. Un résultat qui ne dépend pas des sélections se calcule au chargement.

Essayer dans Qlik Sense

Ce script reprend les exemples de l’article avec les données des schémas, chargées en INLINE : il ne dépend d’aucun fichier ni d’aucune base. Collez-le dans l’éditeur de chargement de données d’une application vide et rechargez. Les expressions à essayer sont en commentaire : créez les graphiques indiqués sur une feuille.

// Aggr() dans Qlik Sense : script d'essai (HeyData)
// Collez-le dans l'éditeur de chargement de données d'une application vide, rechargez,
// puis créez sur une feuille les graphiques décrits ci-dessous.
Sales:
LOAD * INLINE [
Year, Month, Customer, Product, Sales
2025, 1, A, P1, 400
2025, 2, A, P2, 300
2025, 3, A, P1, 500
2025, 1, B, P1, 200
2025, 3, B, P2, 250
2025, 2, C, P2, 900
2025, 3, C, P1, 150
];

// Indicateurs (KPI) :
//   Avg(Aggr(Sum(Sales), Customer))                              → 900 (premier schéma)
//   Avg(Sales)                                                   → 385,7
//   Sum(Aggr(If(Sum(Sales) > 1000, 1, 0), Customer))             → 2 (A et C)
//   Max(Aggr(Sum(Sales), Customer))                              → 1200
//   Avg({<Product = {'P1'}>} Aggr({<Product = {'P1'}>} Sum(Sales), Customer))   → 416,7
//
// Table par Month (deuxième schéma, à droite) :
//   Sum(Aggr(Sum(Sales), Year))     → 2700 sur un seul mois, 0 ailleurs
//   Sum(Aggr(Sum(Sales), Year, Month))  → 600, 1200, 900
//
// Table par Month, cumul trié :
//   Sum(Aggr(RangeSum(Above(Sum(Sales), 0, 12)), (Month, (NUMERIC, ASCENDING))))   → 600, 1800, 2700
//
// Table par Customer et Product :
//   Aggr(NODISTINCT Sum(Sales), Customer)   → le total du client sur chaque ligne
//
// Dimension calculée (les deux meilleurs clients) :
//   =Aggr(If(Rank(Sum(Sales)) <= 2, Customer), Customer)   → A et C

Sources

Aide Qlik Cloud : Aggr - fonction de graphique, syntaxe, NODISTINCT et tri des dimensions.
Henric Cronström, Qlik Design Blog : Pitfalls of the Aggr function, octobre 2015.

Le texte et les exemples sont de HeyData. Cet article fait partie de notre série sur les fonctions et mots-clés méconnus de Qlik Sense : découvrir la série.