Above, Below, RangeSum : les fonctions inter-lignes dans les graphiques Qlik Sense

Above() et le segment de colonne

Above(Sum(Sales)) renvoie la valeur de Sum(Sales) sur la ligne du dessus. Mais « la ligne du dessus » s’entend dans le segment de colonne : l’ensemble des lignes qui partagent les mêmes valeurs pour toutes les dimensions, sauf la dernière. Dans une table par région et par mois, chaque région forme un segment.

Le mois 1 de Sud ne lit pas le mois 4 de Nord : il est en tête de son segment.

La première ligne de chaque segment n’a rien au-dessus : Above() y renvoie NULL. Le qualificatif TOTAL, Above(TOTAL Sum(Sales)), fait de toute la colonne un seul segment.

La syntaxe complète :

Above([TOTAL] expression [, décalage [, nombre]])

• décalage : le nombre de lignes à remonter, 1 par défaut. 0 désigne la ligne courante ; une valeur négative regarde vers le bas, comme Below().
• nombre : au-delà de 1, la fonction renvoie une plage de valeurs, que les fonctions Range*() savent agréger.

Écart, cumul, moyenne mobile

Trois expressions couvrent l’essentiel des besoins :

Sum(Sales) - Above(Sum(Sales))                  // écart avec la ligne précédente
RangeSum(Above(Sum(Sales), 0, RowNo()))         // cumul depuis le début du segment
RangeAvg(Above(Sum(Sales), 0, 3))               // moyenne mobile sur 3 lignes

Les mêmes résultats que la fonction de script Window(), mais calculés dans le graphique.

RowNo() donne le numéro de la ligne dans son segment : utilisé comme nombre, il fait remonter Above() jusqu’à la première ligne. RangeSum() et RangeAvg() ignorent les NULL, ce qui règle le cas des premières lignes. Le résultat est identique à celui de la fonction de script Window() sur les mêmes données, avec une différence de fond : ici, le calcul suit les sélections.

La famille complète

• Below() : la ligne du dessous.
• Top() et Bottom() : la première et la dernière ligne du segment, utiles pour un indice base 100 (Sum(Sales) / Top(Sum(Sales))).
• RowNo() et NoOfRows() : le numéro de la ligne et le nombre de lignes du segment.
• Before(), After(), First() et Last() : les équivalents en colonnes, dans un tableau croisé dynamique, quand la dimension temps est en colonnes.

Ce qui fausse le résultat

Ces fonctions travaillent sur ce qui est affiché, pas sur les données. Trois conséquences :

• Le tri compte. La ligne du dessus dépend de l’ordre de tri du graphique. Qlik Sense désactive d’ailleurs le tri par valeur de mesure dès qu’une expression utilise une fonction inter-lignes.
• Les sélections comptent. Si l’utilisateur exclut le mois 2, le mois 3 a pour ligne précédente le mois 1. L’écart change de sens sans que rien ne le signale.
• Les lignes masquées comptent aussi. Une ligne à zéro masquée, ou une valeur absente, n’existe pas pour Above().

Pour un calcul qui ne doit pas dépendre de l’affichage, deux solutions : un Aggr() avec un tri explicite, décrit dans notre article sur les pièges d’Aggr(), ou un calcul dans le script avec Window() ou Peek().

Sans expression : les modificateurs

Pour les cas simples, les graphiques et les tables de Qlik Sense proposent des modificateurs de mesure, sans écrire de formule : Accumulation, Moyenne mobile, Différence et Nombres relatifs. L’aide Qlik montre qu’ils reposent sur Above(), RangeSum() et RangeAvg() : ils ont donc les mêmes limites. Leur avantage : ils restent lisibles pour quiconque reprend l’application.

À retenir

• La ligne du dessus, dans le segment. Above() lit la ligne précédente parmi celles qui partagent les mêmes dimensions, sauf la dernière.
• TOTAL pour toute la colonne. Sans lui, chaque segment repart de zéro.
• Un nombre pour une plage. Above(expr, 0, 3) renvoie trois valeurs, à agréger avec RangeSum ou RangeAvg.
• RowNo() pour cumuler. RangeSum(Above(Sum(Sales), 0, RowNo())) cumule depuis la première ligne.
• Le résultat suit l’affichage. Tri, sélections et lignes masquées changent la ligne précédente.
• Un modificateur pour les cas simples. Accumulation, moyenne mobile et différence existent sans formule.

Essayer dans Qlik Sense

Ce script charge les données des schémas en INLINE. Collez-le dans l’éditeur de chargement de données d’une application vide et rechargez. Créez ensuite une table avec les dimensions Region et Month, et les mesures indiquées en commentaire.

// Fonctions inter-lignes 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 une table avec les dimensions Region et Month, triée par Region puis Month.
Sales:
LOAD * INLINE [
Region, Month, Sales
Nord, 1, 100
Nord, 2, 120
Nord, 3, 90
Nord, 4, 150
Sud, 1, 80
Sud, 2, 60
Sud, 3, 110
Sud, 4, 70
];

// Mesures à ajouter (premier et deuxième schémas) :
//   Above(Sum(Sales))                             → NULL en tête de chaque région
//   Above(TOTAL Sum(Sales))                       → le mois 1 de Sud lit le mois 4 de Nord (150)
//   Sum(Sales) - Above(Sum(Sales))                → Nord : –, 20, -30, 60
//   RangeSum(Above(Sum(Sales), 0, RowNo()))       → Nord : 100, 220, 310, 460
//   RangeAvg(Above(Sum(Sales), 0, 3))             → Nord : 100, 110, 103,3, 120
//   Sum(Sales) / Top(Sum(Sales))                  → indice base 1 au mois 1 de chaque région
//
// Puis excluez Month = 2 : l'écart du mois 3 se calcule désormais par rapport au mois 1.

Sources

• Aide Qlik Cloud : Fonctions inter-enregistrements dans les graphiques, vue d’ensemble et restriction de tri.
• Aide Qlik Cloud : Above, segments de colonne, plages et exemples de cumul.
• Aide Qlik Cloud : Modificateurs, accumulation, moyenne mobile, différence et nombres relatifs.

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.