Rendre dynamique un workflow et calculer des totaux quand le nombre de colonnes varie

[jcouzy]

On a tous quelque part un workflow qui calcule des totaux avec pour objectif l’analyse de la performance dans le temps.

Ces données sont souvent sous forme de tableau (Excel, mais pas que …) où chaque colonne représente une période (un semestre par exemple).

Le problème est que le nombre de colonnes varie à chaque début de nouvelle période, nécessitant de maintenir nos formules.

Dans cet article, nous allons voir comment nous pouvons dynamiser nos workflows afin qu’ils s’adaptent au nombre de colonnes présentes dans notre fichier source, sans aucune modification !

Prenons un exemple simple d’une table contenant des données de vente sur deux segments d’activité : Segment 1 et Segment 2.

Notre objectif est d’enrichir notre set de données et d’ajouter le total des ventes par Établissement et par Trimestre.

Les données sont fournies par Établissement (lignes) et par semestre (colonnes). Les données sont rafraichies chaque semestre.

Les données durant le semestre 1 (S1) :

Notre objectif est de calculer la somme des ventes quelque soit le segment d’activité. Instinctivement, nous allons utiliser un outil Formule et créer une colonne Total S1 :

Le résultat est sans surprise :

Mais que se passe-t-il quand les données du second semestre sont ajoutées à notre fichier ? Notre fichier d’entrée a 2 nouvelles colonnes :

La formule que nous avons créée précédemment, même si elle marche toujours, est statique et il sera nécessaire de créer une autre colonne Total S2 :

Pas très dynamique… 🤔

Pire encore : quand l’année suivante démarrera, nos colonnes du second semestre disparaîtront de notre source, et le workflow va planter !

Comment rendre cette agrégation dynamique et calculer les totaux quelque soit la période de l’année ?

Nous allons utiliser les outils Transposer et Tableau croisé dynamique pour faire pivoter notre table et rendre nos calculs dynamiques. Voici le workflow que nous allons détailler :

Vous pouvez télécharger l’exemple au bas de cet article

1 - Commençons par l’outil Transposer, qui va nous permettre de convertir nos colonnes en lignes, ce qui nous aidera à faire nos agrégations beaucoup plus simplement :

   

Indépendamment du nombre de colonnes en entrée (S2 présent ou pas), on passe les colonnes en ligne.

La clé est de cocher la case 'colonnes dynamiques ou inconnues' dans cet outil

2 - L’outil Agréger vient par la suite et nous permet de calculer le Total par Etablissement et par Semestre (Regrouper par)

   

3 - Une fois l’agrégation réalisée, on souhaite « rebasculer » nos données en colonnes pour retrouver notre présentation initiale. On utilise l’outil Tableau Croisé Dynamique en spécifiant quelles seront nos colonnes (dans notre cas, une colonne par Semestre) et nos lignes (Etablissement) :

A noter qu’une agrégation est nécessaire pour configurer l’outil Tableau Croisé dynamique, nous sélectionnons Somme mais elle n’aura pas d’impact car nos données sont déjà agrégées.

   

4 - Nous utiliserons l’outil Renommage dynamique afin de corriger les colonnes que nous avons créées à l’aide d’une expression (cette expression unique pourra s’appliquer à un grand nombre de colonnes) :

   

5 - Enfin, nous utilisons une jointure afin de conserver nos données de base, et les enrichir des données agrégées que nous venons de calculer. La jointure se fait sur la colonne Etablissement :

  

Et voilà ! Notre workflow s’adaptera maintenant aux colonnes qui « apparaissent » et « disparaissent » selon la période.

Cet exemple peut évidemment être adapté pour répondre à de nombreux scénarios ( fréquences mensuelles ou quotidiennes, liste variable d’établissements ou d’indicateurs, …), réduisant d’autant le travail de maintenance nécessaire.

Si vous avez des questions ou des commentaires, n'hésitez pas à répondre en bas de l'article ! 

DynamicColumns.zip