6 Replies Latest reply: Oct 2, 2017 2:07 AM by Sébastien Fatoux RSS

    Lignes cumulées (12 mois) par ligne dans un tableau

    Florence Allewaert

      Bonjour à tous,

       

      Je viens vers vous car je cherche depuis plusieurs jours une formule qui me permettrait d'avoir pas ligne, le cumul sur 12 mois.

       

      Je m'explique :

      J'ai un tableau dynamique sur 12 mois où j'ai une colonne CA, Encours et DSO (calcul en fonction de l'épuisement de l'encours).

      Je voudrais avoir un 4eme colonne qui me donnerait la moyenne de mon DSO mais sur 12 mois glissant.

      càd si je sélectionne

      Année = 2017

      Mois = 07

       

      J'obtiens un tableau avec comme dimension la concatenation de Mois +  Année de août 2016 à Juillet 2017 avec CA , Encours et DSO des 12 derniers mois pour chaque ligne . Jusque là pas de problème ...

       

      Je joins en exemple mon tableau

       

      Je voudrais avoir par exemple pour Juil 2017 , la moyenne glissante des 12 mois (Août 2016 à Juil 2017) soit 60,22 (comme dans mon tableau)

      Pour Juin 2017, je voudrais la moyenne glissante du DSO (Juil 2016 à Juin 2017),....

       

      Quelqu'un serait-il m'aider ?

       

      Merci bcp

        • Re: Lignes cumulées (12 mois) par ligne dans un tableau
          Sébastien Fatoux

          Bonjour Florence,

           

          La réponse de Kevin va te donner la réponse correcte que pour la dernière colonne de ton tableau. En effet, Above est une fonction Inter-enregistrement (https://help.qlik.com/fr-FR/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/InterRecordFunctions/inter-record-fun…) d'un tableau, donc même si l'expression spécifie bien de faire la moyenne sur les 12 cellules précédentes à partir de la cellule actuelle, seul la dernière colonne a ces 12 cellules à disposition.

           

          Il faudrait appliquer cette expression dans un tableau contenant toutes les année-mois et n'en rendre visible que les 12 mois désirés.

           

          On peut faire ceci en utilisant la fonction AGGR pour créer un tableau virtuel et des set analysis pour que ce tableau ne soit pas impacté par des sélection temporelle.

           

          Tu n'as pas donné l'expression pour le calcul de DSO, je vais donc utiliser dans mon exemple :  Sum(val_DSO)

           

          L'expression pour le calcul de la moyenne glissante du DSO sur 12 mois serai quelque chose comme :

           

          Only( Aggr({$<Année=, Mois=>} RangeAvg(Above(Sum({$<Année=, Mois=>} val_DSO ),0,2)), Année,  Mois))

           

          Note :

          • L'Aggr doit avoir la même granularité que le tableau dans lequel il est utilisé, afin d'avoir une correspondance direct entre le tableau virtuel (Aggr) et le tableau. Il peut avoir une granularité plus fine si l'expression interne à l'Aggr le demande, dans ce cas il faudra remplacer le Only par une fonction d'agrégation comme Sum, Avg,  
          • Si tu as d'autres dimensions temporelle que l'utilisateur peut utiliser comme sélection (comme Date), il faudra l'ajouter au set analyasis :  {$<Année=, Mois=>}   ==> {$<Année=, Mois=, Date=>}
            • Re: Lignes cumulées (12 mois) par ligne dans un tableau
              Florence Allewaert

              Bonjour Sébastien,

               

              Ta formule me semble assez près de ce que je voudrais mais le souci c'est que je n'arrive pas à faire un set analysis sur ma formule du DSO car elle est assez longue et complexe.

               

              Mon tableau est dynamique en fonction du Mois et de l’année sélectionnée

               

              Exple , je choisis juillet, j’ai un tableau reprenant mes valeurs de août 2016 => Juil 2017

              Fichier 1.JPG

               

              Si je sélectionne, le mois de juin, j’ai Juil 2016 => Juin 2017

               

              Fichier 2.JPG

               

              Le problème de ma colonne DSO est que le calcul est assez long et « complexe »

               

              ************************************************************************************************************************

              if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))<0,
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))/
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))*30,

              if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)<0,
              ((
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_Encours))-
              Sum({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC)))/
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1))*30+30,


              if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)<0,
              (((
              sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_Encours))-
              Sum({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC)))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1))/
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2))*30+60,


              if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3)<0,
              (((
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_Encours))-
              Sum({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC)))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2))/
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3))*30+90,

              if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC4)<0,
              (((
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_Encours))-
              Sum({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC)))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3))/
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC4))*30+120,

              if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC4)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC5)<0,
              (((
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_Encours))-
              Sum({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC)))-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3)-
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC4))/
              Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC5))*30+150,'>180 days'))))))

              **********************************************************************

               

              J’ai essayé de le mettre en variable mais du coup, il ne tient plus compte du nombre de Mois séquentiel ( = nombre qui correspond aux dates)fichier 3.png

               

              De plus si je veux l’utiliser dans un Set Analysis , ce n’est pas possible puisque ma variable fait déjà une somme (je suis obligée pour que le résultat soit correct)

               

               

              Merci bcp pour ton aide

               

              Florence