En ce moment je m’amuse beaucoup avec Tableau, et je pense avoir atteint le stade de la compétence consciente – ou bien je suis en pleine désillusion, on ne sait jamais vraiment… Mais à force de formation, de démonstrations, de maquettes, de prototypes et de webcasts (je vous recommande l’advanced training, « live online » tous les mercredi soir à 20h), je commence à me sentir bien avec le produit. La technique d’un côté plus la théorie de l’autre, je maîtrise mon processus de montée en compétence!

Tout ça pour dire que ça fait longtemps que je n’ai pas fait un petit article technique, et que désormais je me sens assez confiant sur Tableau pour me lancer. Evidemment c’est Tableau, donc « technique » c’est un bien grand mot. Avec ce soft on parle plus de recettes, d’astuces, que de code pur et dur. Vous allez voir !

Pour la problématique à traiter je suis parti trouver l’inspiration sur l’excellent blog PowerPivotPro, définitivement le meilleur blog sur PowerPivot à l’heure actuelle. Si d’habitude le blog est tenu par le merveilleux Rob Collie, pour le sujet qui nous intéresse l’auteur du jour est David Churchward. Un grand merci à eux pour cette source d’inspiration (et le classeur de données), et vous l’aurez compris mon objectif va être de résoudre le même cas métier qu’eux en utilisant Tableau plutôt que PowerPivot. Voici les liens vers la série d’articles qui nous intéresse : 1, 2 et 3, j’encourage vivement ceux qui savent ce que DAX veut dire d’aller les consulter, ne serait-ce que pour leur culture personnelle.

Le cas métier est le suivant : calculer les commissions des commerciaux selon un barème par tranche. Pour ce faire on utilisera deux méthodes : la prise en compte du taux maximal comme unique coefficient sur les ventes d’une part, et  d’autre part la ventilation de ce même calcul sur les tranches. Pas de panique, on va détailler ça.

Tout repose sur une table des taux de cette forme :

Mes commerciaux sont commissionnés tous les mois. Chaque mois on va donc agréger leur chiffre des ventes par type de produit, puis déterminer la ligne de taux de commission applicable en fonction de sa date de validité et du montant des ventes réalisés. Vu qu’on est pas (complétement) pervers, on pose comme hypothèse qu’un taux ne peut pas changer dans un même mois.

Par exemple, en février 2012 Bob a vendu 80 000$ de café (ça en fait des dosettes) et 60 000$ de thé. Sur cette période (du 01/02/2012 au 29/02/2012), le taux applicable pour ce montant de café est 20% (deuxième ligne du tableau ci-dessus), le taux applicable pour ce montant de thé est 30% (dernière ligne du tableau).

A partir de là, deux méthodes de calcul sont possibles :

• En taux maximal, Bob touchera 34 000$, soit 20% * 80 000 + 30% * 60 000
• En ventilation par tranche, Bob touchera 15 000$ pour le café soit 10%*10 000 sur la première tranche plus 20%*70 000 sur la deuxième tranche. On ventile son résultat sur chaque taux intermédiaire plutôt que sur le taux maximal. Il touchera également 17 000$ pour le thé soit 10%*5 000 sur la première tranche plus 30%*55 000 sur la deuxième. Ce calcul est le même que pour nos impôts sur le revenu en fait. Avec cette formule Bob touchera au total 32 000$, évidemment moins qu’avec le taux maximal.

Voilà pour le fond du problème. Si vous avez besoin de plus d’explications, n’hésitez pas à consultez l’article original ou à poser des questions en commentaire.

D’un point de vue source de données, je vais brancher Tableau Desktop directement sur le classeur qu’utilise David Churchward (lien tout en bas de la page). Evidemment je pourrais exploiter le résultat de ses efforts et aspirer directement les infos de son tableau croisé dynamique final, une manière tout à fait convenable dans le quotidien, mais le but de cet article c’est de voir comment implémenter le calcul dans Tableau, donc je ne vais prendre que ses onglets sources. J’en profite pour rappeler que Excel+PowerPivot et Tableau Desktop sont très, très bons amis. Les limites de l’un étant très bien compensées par les forces de l’autre, et vice et versa. Mais c’est un sujet pour un autre jour !

Je dispose donc des éléments suivants :

• Des commandes :

• Des gens (pour faire joli):

• Des types de produit (pour faire joli encore):

• Des taux par type, par période (From_Date, To_Date) et par tranche de montant (From, To) :

Vous noterez que pour le moment j’ai simplifié la problématique complète résolue par David Churchward. De son côté il calcule des commissions pour les commerciaux sur leurs ventes, et pour les managers en fonction du résultat de leurs commerciaux. De mon côté je ne calcule que les premières. A mon sens la mécanique est la même, si j’ai le temps je compléterai mais je ne vois pas de difficultés supplémentaires.

Je lance donc Tableau et je me connecte au fichier Excel (note pour les utilisateurs d’Excel 64bit, Tableau peut vous demander de télécharger un driver complémentaire, pas de panique, le message d’erreur est explicite et le driver est ici).

J’ouvre une nouvelle connexion de données de type Excel et je pointe vers le fichier source :

On sent bien dans notre cas que tout le problème réside dans la jointure entre les taux (Rates) et les commandes (Orders). Mon premier réflexe ici est donc de monter 2 connexions de données, une contenant les commandes auxquelles je vais joindre les types de produits et les managers, et une seconde contenant les taux. Une fois les données chargées je pourrai toujours créer un champ calculé avec le langage d’expression de Tableau pour calculer mes commissions.

Roulez jeunesse, je me connecte aux commandes (Orders) et je joins les tables People et Types :

On repasse par l’écran d’accueil pour créer dans le même classeur Tableau une deuxième connexion en parallèle de la première :

Et j’obtiens les champs tant attendus dans Tableau :

Je peux donc monter une première visualisation qui va mélanger mes données en faisant juste un drag and drop de mes champs… ou pas :

Evidemment ce n’est pas magique ! La jointure est complexe, et Tableau ne gère que les LEFT JOIN simples entre les datasets qui ont déjà été chargés.

Ok pour que ça ne marche pas tout seul, j’essaye donc de définir un champ calculé qui vaudrait le taux souhaité quand les conditions de jointures sont bonnes, et 0 sinon, mais sans grand succès.

En effet j’essaye de brasser des données qui n’ont pas du tout le même niveau de granularité. Ne serait-ce que par rapport au temps, dans mon dataset des commandes je suis au jour de la commande, du côté du dataset des taux ma granularité temporelle est la période de validité d’un taux (From/To), et j’attends un résultat au mois… Délicat.

Tableau est avant tout un outil de visualisation, s’il est tout à fait capable de faire des calculs à des niveaux d’agrégation intermédiaires sans difficulté, il nécessite que ces niveaux apparaissent dans la visualisation ! Alors prévoir tout ça dans une visualisation c’est certainement possible pour un jedi, mais pour le commun des mortels, c’est un peu trop velu. Il va falloir trouver une autre manière de faire.

Notez au passage que de son côté PowerPivot se régale de ce genre de problématiques. Son langage d’expression, le DAX, dispose d’une syntaxe qui permet « facilement » d’exprimer des calculs intermédiaires à différents niveaux d’agrégation. Mais on a déjà dit qu’on voulait traiter ce cas avec Tableau, alors insistons un peu !

Mais pas trop! Car heureusement Tableau a plus d’un tour dans son sac, dont un qui me plait particulièrement : il peut attaquer les fichiers Excel en SQL. Oui Monsieur, oui Madame ! Et le SQL c’est un peu mon premier amour, autant vous dire qu’à partir de là, pour moi, le problème se simplifie 🙂

On ferme la connexion sur les taux, elle ne sert à rien, et on modifie la connexion sur les commandes en passant en mode SQL :

Voici le SQL que j’ai bricolé :

SELECT
       YEAR([Orders$].[Date]) AS [Order_Year]
       ,MONTH([Orders$].[Date]) AS [Order_Month]
       ,[Orders$].[Salesman] AS [Salesman]
       ,[Orders$].[Type] AS [Type]
       ,[Types$].[Type_Name] AS [Type_Name]
       ,[People$].[Manager] AS [Manager]
       ,['Rates (2)$'].[From] AS [From]
       ,['Rates (2)$'].[To] AS [To]

       ,SUM([Orders$].[Value]) AS [Value]
       ,MAX(['Rates (2)$'].[Rate]) AS [Rate]

FROM ( ( [Orders$]
  INNER JOIN [People$] ON [Orders$].[Salesman] = [People$].[Name] )
  INNER JOIN [Types$] ON [Orders$].[Type] = [Types$].[Type_Code] )
  INNER JOIN ['Rates (2)$'] ON
             ([Orders$].[Type] = ['Rates (2)$'].[Type])
       AND ([Orders$].[Date] >= ['Rates (2)$'].[From_Date])
       AND ([Orders$].[Date] <= ['Rates (2)$'].[To_Date])

WHERE
       ['Rates (2)$'].[Rate_Group] = 'Salesperson'

GROUP BY
       YEAR([Orders$].[Date])
       ,MONTH([Orders$].[Date])
       ,[Orders$].[Salesman]
       ,[Orders$].[Type]
       ,[Types$].[Type_Name]
       ,[People$].[Manager]
       ,['Rates (2)$'].[From]
       ,['Rates (2)$'].[To]

Pour ceux que ça effraie, je vous en fais une relecture rapide (cliquez dessus pour le voir en grand):

Le dataset ainsi composé renvoie les lignes suivantes:

Vous  noterez qu’à ce moment précis mes données sont « fausses ». En effet le vendeur Adam sur le type AV sur janvier 2012 a ses 54239$ de ventes répétés 4 fois (4 premières lignes au-dessus). C’est complétement normal, on va corriger ça juste après.

Je me permets de faire vite fait sur les dates dans le SQL (juste Mois et Année) parce que je vais utiliser un champ calculé dans Tableau pour régénérer une vraie date complète, qui elle me permettra d’utiliser toutes les fonctions temporelles de l’outil :

Maintenant que nous sommes dans Tableau, on va recalculer un montant des ventes correct, qui ignore la démultiplication qu’on a observé plus tôt. Pour se faire on va créer un champ calculé qui vaut 0 quand le montant des ventes n’est pas dans la tranche du taux, et ce même montant si c’est le cas. On va dé-multiplier la démultiplication :

Une fois qu’on a ce montant, calculer les commissions sur taux maximal devient enfantin :

Je vous illustre ça tout de suite pour bien comprendre ce qu’on vient de faire :

On part de Value qui est retourné par la requête SQL sur toutes les tranches. Sales est passé à 0 si Value n’est pas entre From et To. Et Commission vaut Rate que multiplie Sales. Pas mal non ?

De la même manière, on va calculer un montant de ventes qui lui sera ventilé sur les taux :

Ok, celle-là est un peu plus sioux. Voilà l’opération réalisée ligne par ligne :

Résultat qu’on utilisera pour calculer les commissions ventilées :

Et voilà ce que ça donne au niveau fin, pour bien comprendre la mécanique :

Enfin, voici le résultat final à niveau agrégé. Notez que pour les sales on peut utiliser les 2 mesures qu’on a calculé puisqu’elles s’agrègent correctement (sales et sales (tiered)), mais pas le value original, évidemment :

Pas si difficile que ça en fin de compte ? 🙂

Et pour ceux qui trouveraient que la syntaxe SQL et les champs calculés sont complexes, voici le DAX qui réalise la même chose côté PowerPivot :

=IF(COUNTROWS(VALUES(Dates[MonthEndDate]))=1
   &&COUNTROWS(VALUES(People[Name]))=1
   &&COUNTROWS(VALUES(Types[Type_Code]))=1,
     IF([Sales_Value]=BLANK(),BLANK(),
       CALCULATE([Sales_Value]-MAX(Rates[From]),
          FILTER(Rates,
             Rates[From]<=[Sales_Value]
             &&Rates[To]>=[Sales_Value]
             &&Rates[From_Date]<=MAX(Dates[MonthEndDate])
             &&Rates[To_Date]>=MAX(Dates[MonthEndDate])
             &&Rates[Type]=VALUES(Types[Type_Code])
             &&Rates[Rate_Group]=”Salesperson”
                 )
                )
        ),
    IF(COUNTROWS(VALUES(Dates[MonthEndDate]))=1
      &&COUNTROWS(VALUES(People[Name]))>1
      &&COUNTROWS(VALUES(Types[Type_Code]))=1,
       IF([Sales_Value]=BLANK(),BLANK(),
         CALCULATE([Sales_Value]-MAX(Rates[From]),
          FILTER(Rates,
             Rates[From]<=[Sales_Value]
             &&Rates[To]>=[Sales_Value]
             &&Rates[From_Date]<=MAX(Dates[MonthEndDate])
             &&Rates[To_Date]>=MAX(Dates[MonthEndDate])
             &&Rates[Type]=VALUES(Types[Type_Code])
             &&Rates[Rate_Group]=”Manager”
                 )
                )
        )
      )
)

Après tout est question de goût 😉

Alors voilà, on a chargé nos infos dans Tableau, on les a vérifié, c’est maintenant que le vrai travail de visualisation de données peut commencer. Moi je vais interroger les managers pour connaître leurs interrogations et construire les graphes qui y répondront… Je vous laisse faire de même et poursuivre avec le classeur que voici (à télécharger à travers Tableau Public ci-dessous, en bas à droite de la page qui s’ouvre).

Amusez-vous bien 🙂