Calculs multi-niveaux avec les formules LOD

Présentation

Au chapitre Aller plus loin avec les calculs de table, nous avons vu comment les calculs de table permettent de rajouter un troisième niveau de calcul par-dessus celui des données fines et celui de l’agrégation. Nous allons maintenant aborder les expressions LOD, qui permettent de calculer une agrégation en définissant le niveau de détail à prendre en compte (en anglais, level of detail, d’où l’abréviation LOD). Si les expressions LOD sont relativement complexes, elles vous garantissent de pouvoir atteindre n’importe quelle donnée à partir de n’importe quel emplacement d’un graphique, afin de réaliser précisément le calcul que vous souhaitez mettre en place.

Les expressions LOD se caractérisent par l’emploi des {accolades} et se subdivisent en trois types : EXCLUDE, INCLUDE et FIXED. Nous allons commencer par le plus simple.

Ignorer une dimension avec EXCLUDE

Nous allons commencer par monter quelques statistiques simples afin de mieux cerner le rôle et l’intérêt de notre première expression LOD. Notre objectif sera de dégager, pour la responsable marketing d’AlimOL, la performance relative de chaque produit au sein de sa gamme.

Au besoin, téléchargez le fichier Ventes_AlimOL.txt depuis la page Informations générales, puis créez un nouveau classeur Tableau et connectez-le au fichier.

Si vous ne l’avez pas déjà fait, créez un champ calculé nommé CA avec la formule [Prix Unitaire] * [Quantité]

Créez une nouvelle feuille nommée Calcul Performance par produit et filtrez-la sur l’année 2024.

Placez les champs Nom Rayon, Nom Gamme et Produit en Lignes, et le champ CA sur la propriété Texte.

Dans le menu Analyse - Totaux, activez les options Afficher les totaux généraux des colonnes et Ajouter tous les sous-totaux.

Appliquez un tri Imbriqué par Somme du CA décroissante sur chacune des trois dimensions.

Performance de chaque produit et de chaque gamme

Nous obtenons ainsi un tableau du chiffre d’affaires par produit, avec tri et sous-total à chaque niveau hiérarchique. Pour mettre en valeur les performances relatives de chaque produit, la première étape va être de calculer dans chaque gamme le chiffre d’affaires d’un produit moyen (ce qui nous permettra ensuite de déterminer l’écart à cette moyenne). Pour les viandes par exemple, il y a six produits pour un total de 36 553 €, le chiffre d’affaires du produit moyen est donc 36 553 / 6 ≈ 6 092,1666666…

Comment reproduire ce calcul dans Tableau ? Ce calcul consiste à diviser la somme du chiffre d’affaires de la gamme par le nombre de produits différents qu’elle contient, il suffit donc de transposer ce raisonnement dans le langage de calcul de Tableau :

Créez un champ calculé nommé CA du produit moyen, avec la formule ci-dessous :

SUM([CA]) / COUNTD([Produit])

Faites un double clic sur le nouveau...

Ajouter une dimension avec INCLUDE

Si EXCLUDE consiste à ignorer une dimension présente, le deuxième type d’expression LOD est son symétrique la fonction INCLUDE, qui permet d’ajouter une dimension absente du graphique.

Préparation

La responsable marketing d’AlimOL voudrait savoir comment évolue le comportement des clients, et en particulier savoir si le client moyen achète de plus en plus, ou si la croissance n’est due qu’à l’arrivée de nouveaux clients.

Ce calcul de client moyen est proche du calcul de produit moyen que nous avons réalisé pour le graphique précédent : sur le principe, il faudrait faire une somme du chiffre d’affaires par client puis calculer la moyenne de ces sommes ; dans la pratique, nous allons plutôt faire le ratio entre la somme du chiffre d’affaires et le compte distinct des clients. Cette formule donne le même résultat, nous évite d’avoir deux niveaux de calcul successifs et peut être utilisée sur n’importe quelle combinaison de filtres et de dimensions.

Créez un champ calculé nommé Client avec la formule ci-dessous :

[Prénom] + " " + [Nom]

Créez un second champ calculé nommé CA du client moyen avec la formule ci-dessous :

SUM([CA]) / COUNTD([Client])

Créez une nouvelle feuille nommée Calcul du client moyen.

Glissez le champ Date Comm sur l’étagère Lignes et le champ CA sur la propriété Texte, puis faites un double-clic sur la gélule CA du client moyen pour activer le système Noms de mesures / Valeurs de mesures.

Nous voudrions ajouter le nombre de clients, mais le champ Client est une dimension et l’étagère Valeurs de mesures refuse de l’accepter....

Figer la dimensionnalité et ignorer les filtres avec FIXED

FIXED, le troisième type d’expression LOD, diffère des deux premiers par deux aspects :

INCLUDE et EXCLUDE viennent modifier la dimensionnalité de la position calculée ; au contraire, FIXED ignore totalement la dimensionnalité de la position calculée et n’applique que sa propre dimensionnalité.
Dans l’ordre des opérations, les formules avec FIXED sont calculées après les filtres de contexte mais avant les filtres de dimension ; les formules avec INCLUDE ou EXCLUDE, eux, sont calculés juste après les filtres de dimension.

Nous allons tester ces deux aspects.

Effet de FIXED par rapport aux dimensions

Pour illustrer le mécanisme du FIXED, nous allons refaire le calcul du client moyen : tel que nous l’avons utilisé sur le tableau de calcul, c’est la somme annuelle du chiffre d’affaires divisée par le nombre annuel de clients distincts.

Dupliquez la feuille Calcul du client moyen, nommez le duplicata Calcul du client annuel moyen. Sur l’étagère Valeurs de mesures, conservez les gélules SOMME(CA), CPTD(Client) et AGG(CA du client moyen) etsupprimez les autres.

Créez un champ calculé nommé CA annuel ; dans la formule, commencez à taper {FIXED, puis faites glisser la gélule ANNÉE(Date Comm) à la suite. Complétez ensuite pour obtenir la formule ci-dessous :

{FIXED DATEPART('year', [Date Comm]) : SUM([CA])}

Dupliquez le champ CA annuel, nommez le duplicata Nb clients annuel et adaptez la formule ainsi :

{FIXED DATEPART('year', [Date Comm]) : COUNTD([Client])}

Créez un troisième champ calculé, nommé CA annuel du client moyen, avec cette formule (les deux champs CA annuel et Nb clients annuel sont des LOD, on doit donc les agréger) :

SUM([CA annuel]) / SUM([Nb clients annuel])

Glissez les trois nouveaux champs sur l’étagère Valeurs de mesures.

Depuis le volet Données, glissez la dimension fictive Noms de mesures sur la propriété Couleur. Cliquez ensuite sur la propriété Couleur, faites Modifier les couleurs, puis mettez en vert les trois premières...

Imbriquer des expressions LOD

Comme nous l’avons vu à travers ces différents exemples, les expressions LOD s’appliquent à un agrégat et en font une valeur détaillée ; dans la plupart des cas, cette valeur détaillée est à son tour agrégée. Là où Tableau est particulièrement puissant, c’est qu’il est capable de faire subir aux données plusieurs allers-retours entre niveau agrégé et niveau détaillé. Autrement dit, le résultat d’une formule LOD peut être embarqué dans une nouvelle formule LOD.

Préparation

Suite à nos conclusions sur la moyenne tirée vers le haut par quelques gros clients, la responsable marketing souhaite s’intéresser à ces gros clients. Elle voudrait donc que nous ajoutions à notre graphique une courbe représentant le chiffre d’affaires du plus gros client de chaque période, et que nous mettions son nom en étiquette.

Déterminer le chiffre d’affaires du plus gros client est assez simple : il faut utiliser INCLUDE pour faire la somme par client, puis agréger les résultats par la fonction MAX.

Dupliquez la feuille Evolution du client moyen et du client médian, nommez le duplicata Client médian, client moyen et meilleur client.

Dans le volet Données, dupliquez le champ CA du client médian et nommez le duplicata CA du meilleur client.

Modifiez la formule pour remplacer MEDIAN par MAX :

MAX({INCLUDE [Client] : SUM([CA])})

Où placer cette information ? Au final, nous devrons afficher le nom du meilleur client en étiquette, ce qui veut dire que les étiquettes de cette troisième courbe devront être différentes de celles des deux autres. Nous allons donc utiliser un système d’axe double :

Glissez le nouveau champ sur l’étagère Lignes, passez en Axe double, Synchronisez le nouvel axe et pour finir masquez-le (faites un clic droit sur l’axe de droite et décochez Afficher l’en-tête).

Activez l’étagère Repères AGG(CA du meilleur client) et glissez le champ CA du meilleur client sur la propriété Étiquette.

Activez l’étagère...

Exercices : participation aux élections européennes

Nous allons maintenant retrouver les données de participation aux élections européennes ; vous pouvez reprendre votre classeur du chapitre Requêtes complexes, ou bien en télécharger le corrigé chapitre 11.twbx depuis la page Informations générales.

Participation moyenne en Europe

La dernière version que nous avons faite de la carte du taux de participation aux élections européennes présente des bulles indiquant le taux de participation par un dégradé de couleur. Nous aimerions bien renforcer ce codage couleur, par exemple en passant sur une palette rouge-vert divergent, mais cela suppose de définir un seuil séparant un bon taux de participation d’un mauvais taux de participation, ce qui est assez subjectif…

Une manière d’objectiver ce seuil serait de déterminer quel est le taux de participation moyen ; nous aurions alors une justification pour attribuer des nuances de vert aux pays au-dessus de la moyenne, et des nuances de rouge aux pays en dessous. L’idéal serait de pondérer ce calcul de moyenne par le nombre d’électeurs inscrits, mais comme nous n’avons pas cette donnée, nous allons nous rabattre sur le nombre de sièges, qui indique bien la représentativité politique de chaque pays.

Exercice 1 : sur une nouvelle feuille filtrée par année électorale, présentez la participation...

Solutions commentées : participation aux élections européennes

Participation moyenne en Europe

Exercice 1 : sur une nouvelle feuille filtrée par année électorale, présentez la participation et le nombre de sièges de chaque pays, avec le total général.

Pas de difficulté particulière sur cet exercice :

Créez une nouvelle feuille nommée par exemple Test Moyenne UE.

Glissez le champ Année électorale sur l’étagère Filtres, choisissez par exemple 1979.

Glissez le champ Country Id sur l’étagère Lignes et le champ Participation sur la propriété Texte.

Faites un double clic sur le champ Nb Sièges pour faire apparaître l’étagère Valeurs de mesures.

Déplacez la gélule SOMME(Nb Sièges) après celle de Participation.

Allez dans le menu Analyse - Totaux et choisissez Afficher les totaux généraux des colonnes.

Travail préparatoire

Exercice 2 :créez un champ calculé nommé Participation Moyenne UE, dont la formule doit calculer la participation moyenne pondérée par le nombre de sièges attribué à chaque État. Ajoutez ce champ au tableau de l’exercice précédent afin de vérifier le calcul.

Le champ Participation contient la fonction ATTR, ce qui nous interdit de l’utiliser dans une agrégation. Il faut revenir à sa source, le champ masqué Rate, et le diviser par 100.

Ouvrez le menu en haut à droite du volet Données et choisissez Afficher les champs masqués.

Faites un clic droit sur le champ Rate et choisissez Afficher (ou cliquez sur le symbole représensentant un œil barré).

Ouvrez à nouveau le menu en haut à droite du volet Données et décochez Afficher les champs masqués.

Créez un champ calculé nommé Participation Moyenne UE avec la formule ci-dessous :

SUM([Rate] * [Nb Sièges]) / SUM([Nb Sièges]) / 100

Faites un clic droit sur le nouveau champ, choisissez Propriétés par défaut et appliquez-lui un Format de nombre en Pourcentage avec une décimale.

Glissez le nouveau champ sur l’étagère...