Comment générer et formater des totaux et cumuls

[StephaneP]

Comment générer et formater des totaux et cumuls

Je me souviens encore de la 1^ère fois où j’ai voulu faire des totaux et sous totaux avec Alteryx. Je me sentais démuni.

Un peu comme si j’avais une main attachée dans le dos pour faire un CTRL+ALT+SUP.

Ce n’est pas du tout la même approche que dans Excel, ce qui explique le petit malaise du démarrage, en revanche une fois maitrisée cela peut être nettement plus dynamique et maintenable dans le temps. Surtout qu’avec l’outil Table on peut également formater le résultat dynamiquement.

Pour vous faire gagner du temps, voici donc un tour d’horizon des principales techniques pour réaliser les différents types de totaux ou sous totaux verticaux ou horizontaux.

Souvenez-vous, il n’y a jamais qu’une façon de faire dans Alteryx. Piochez des idées dans ces exemples et en fonction du niveau de dynamisme ou de robustesse créez la solution la plus adaptée pour vous.

Hormis les cas simples, le principe général reste de calculer séparément les totaux ou sous totaux puis de les recombiner dans le bon ordre. La gestion de l’ordre est finalement la problématique principale à gérer.

A noter que j’ai placé un outil Table en bout de chaîne pour vous montrer des exemples de formatage statique ou dynamique de ce type de tableau. Il est important de réaliser ce qu’on peut faire et que ce n’est pas compliqué. Il faut juste savoir comment. Notamment la série d’articles Reporting avancé ou le paradoxe de Spock (Le reporting avancé ou le paradoxe de Spock : 1-Le Starter kit du reporting avancé, Reporting avancé ou le paradoxe de Spock : 2. Comprendre la logique de formatage) est là pour vous guider pas à pas.

Tous les exemples présentés ici se trouvent dans le workflow en fin d’article.

A - Colonne de Total par ligne

Exemple 1: Insérer une colonne de Total par Ligne

La base. Cela peut se faire en dur via l’outil formule, ou en dynamique via une combinaison très pratique et très utilisée dans différent domaine : Transposer + Tableau Croisé dynamique.

On exploite les options Méthode d’agrégation des valeurs en bas du menu et peu connues. Elles permettent de rajouter automatiquement des sommes en ligne ou colonne.

A noter que cette technique rajoute des préfixes (Sum_ Count_... suivant l’option). D’où la présence de l’objet Attribution dynamique d’un nouveau nom, qui permet de supprimer dynamiquement ce préfixe.

L’intérêt de cette combinatoire est qu’elle n’est pas dépendante du nombre de colonnes en entrée si on active bien l’option Colonnes dynamiques ou inconnues dans le 1^er outil Transposer.

Si demain une nouvelle colonne apparait et doit être sommée elle le sera par ce biais.

B - Ligne de Total par Colonne

Exemple 2 : Insérer une ligne de Total par colonne

Là aussi une version statique (on agrège à part puis on consolide les 2) et la même technique combinée que pour l’exemple précédent.

Exemple 3 : Insérer un sous total pour une sous-catégorie EN DUR

Le principe est d’insérer un sous total partiel sur une combinaison déterminée, en dur, de valeur.

Il faut donc gérer la liste des valeurs (ici traitée en dur dans le filtre), sommer les différentes composantes puis ordonner correctement les résultats.

A noter l’option dans l’outil Union qui permet de gérer l’ordre de consolidation verticale des différents tableaux et permet de recombiner correctement nos différentes composantes.

Note : Par défaut l’outil Union trie les entrées par ordre alphabétique. Cette astuce peut vous aider à gérer l’ordre sans cette option en renommant les liens d’entrée. Par défaut, il créée des #1, #2 lors de la création des liens. #1 sera le 1^er bloc puis #2 et ainsi de suite. Vous pouvez les renommer en cliquant sur le lien et gérer ainsi visuellement dans le workflow l’ordre de combinaison.

Exemple 4 : Insérer des sous totaux dynamiques sur la base des valeurs d'une colonne particulière

(ici par département, Marketing ou Production)

Un grand classique : générer un sous total sur la base d’une catégorie / caractéristique présente dans une colonne. Ici, on veut générer un regroupement sur la base de la colonne Dept (Mkt ou Prod) et bien sûr que ce soit dynamique. On veut aussi générer le Total global.

Le principe consiste à :

• Prendre une photo de l’ordre des lignes de détail
• Générer 1 branche par niveau d’agrégation avec l’ordre des nouveaux sous totaux
• Ajouter via une formule les colonnes ou valeur manquantes et leur accoler une valeur (ex : pour le Total global on n’a pas de valeur pour le Dept ou Sous Dept). Ceci permet d’avoir des structures de table homogène qu’on va pouvoir consolider simplement
• Gérer l’ordre des blocs de données sur la base de l’ordre des lignes initiales en ajoutant les sous totaux aux bons endroits

Avec cette méthode quel que soit le nombre de lignes par Dept cela fonctionne et quelque soit le nombre de ces Dept aussi.

Le fait de « prendre une photo » de l’ordre des lignes permet d’éviter les problèmes de tri si on ne se basait que sur les labels Dept/ Sous Dept. Le fait de rajouter « Total » à la fin n’est pas toujours pertinent.

Exemple qui poserait problème: Si j’avais 2 Dept labelisé « MKT » et « MKT T2 », suite au tri par ordre alphabétique les sous totaux seraient placés après toutes les lignes de détail. L’affichage serait faux.

A noter que le formatage peut aussi être rendu dynamique si vous normalisez les désignations.

Ici par exemple ce sont les lignes qui contiennent « Total » dans les 5 caractères de droite qui se verront appliquer un color coding conditionnel. J’ai aussi fait des cas particuliers de couleur par département pour mieux les identifier mais on n’est pas obligé si on veut que cela reste dynamique.

Exemple 4 BIS : Insérer des sous totaux dynamiques sur la base des valeurs d'une colonne particulière. Autre type de formatage.

Même idée que précédemment, on cherche à automatiser la génération de sous totaux et totaux sur la base des valeurs d’une colonne, ici Category (Private ou Public).

Il y a 2 nuances avec l’exemple précédent :

1 - L’ordre est ici formalisé via une colonne qui porte cette information. Cela a du sens lorsqu’on commence à avoir de nombreux niveaux pour ne pas s’y perdre et pouvoir tout remettre d’aplomb sur la base de tri. C’est donc une étape dédiée par niveau d’agrégation. Dans l’exemple précédent, cela était géré par un enchainement d’Union dans le bon ordre. Mais à grande échelle, la maintenance devient complexe. D’où cette technique.

2 - Le formatage de sortie est aussi différent pour vous montrer d’autres astuces (notamment comment faire disparaitre du texte en basculant la couleur d’un texte pour l’aligner avec celle du fond).

C - Cumul glissant

Exemple 5: Générer automatiquement un cumul glissant regroupé par colonne

(ici par département)

Petite nuance ici puisqu’on va générer des cumuls glissants. On souhaite en avoir 2. L’un qui soit un cumul global et l’autre qui soit un cumul au sein de chaque Department.

On se sert d’un outil dont c’est la fonction : le Total Cumulé.

Il possède une option qui permet de faire ce cumul segmenté par la valeur d’une colonne.

A noter ici que pour mettre en valeur les sous totaux intermédiaires, le formatage est principalement géré via les règles de formatage de colonnes. On empile plusieurs formatages de table, lignes et colonnes. Il faut apprendre à maitriser les ordres de priorités. Se référer à la série « Reporting Avancé ou le paradoxe de Spock » (Lien du 1er article: Le reporting avancé ou le paradoxe de Spock : 1-Le Starter kit du reporting avancé) si vous souhaitez des explications plus détaillées.

Note sur le cumul glissant par colonne

Dans la logique Alteryx, pour faire un cumul glissant par colonne, il faut d’abord basculer les colonnes en ligne via un Transpose, appliquer le Total Cumulé vertical, puis rebasculer en colonne via un Tableau croisé dynamique.

D - Remarque finale sur le formatage

Si vous souhaitez formater vos tableaux finaux, l’outil Table n’est qu’une des options à disposition.

On peut pousser:

• vers du Excel déjà formaté (l’équivalent d’un copier/coller valeur en quelques sorte),
• vers une feuille de données puis construire dessus des tableaux et graphiques dans Excel,
• vers des outils de Dataviz (Tableau, PowerBI, Qlik…).

La principale force de l’outil Table est de générer ce même rapport en masse en le déclinant sur différents axes métiers. Combiné à l'outil Rendu (Tour d'horizon ici ) on peut alors le décliner automatiquement sous différent formats de sortie (pdf, xls, mail, ppt, word, html…) et profiter du dynamisme du format pour supprimer toute intervention manuelle.

A ce propos, la plupart du temps le formatage peut être rajouté à postériori à la suite de la construction de la table de donnée. Mais pour du formatage dynamique avancé, il faut parfois rajouter à l’avance des colonnes ou lignes techniques contenant des paramètres utilisés par le formatage conditionnel pour s’avoir où s’appliquer (ex : niveau dans la hiérarchie, anomalie sur la ligne, colonne périodique ou de cumul…). On peut dans l’outil table avoir ces valeurs à disposition mais ne pas les afficher dans la sortie.

Ici par exemple le contenu de RecordID peut être utilisé dans les formules de formatage conditionnel sans apparaître dans le tableau final.

Et voila, j’espère que maintenant vous pourrez construire ces petites additions sans même réfléchir.

Enjoy !

Comment gérer et formater des totaux et cumuls.yxmd

[StephV]

Merci @StephaneP pour tous ces tips ! Je suis sûre que cela va être très utile pour les membres de notre communauté ! Merci encore pour le partage ! Bonne journée ! 

[StephaneP]

Bonjour à tous,

Pour votre information, l' Exemple 4 : Insérer des sous totaux dynamiques sur la base des valeurs d'une colonne particulière, a été mis à jour. Il permet désormais de prendre en compte les cas où l'ordre alphabétique n'est pas pertinent pour ordonner l'ordre des lignes et sous totaux. L'idée est de prendre une photo de l'ordre des lignes avant d'ajouter les sous totaux ce qui permet de les replacer au bon endroit sans se base sur l'ordre alphabétique.

Enjoy