7 Replies Latest reply: Sep 24, 2013 1:34 PM by Anquetil Christophe RSS

    Est-ce possible

    Anquetil Christophe


      Bonjour à tous,

       

      Je suis devant un problème de conception dans mon script.

      Je souhaiterai regrouper des données de ventes (Chiffre d'affaires) par périodes avec un cumul sur les 3 et/ou 6 derniers mois de vente (distinguées par la colonne D et E).

      Et cela généré à chaque période de vente.

       

      Voici en sur Excel ce que je souhaiterai (valeur souhaitée G).

      Help_script.png

      L'origine des données est une requête SQL.

       

      Y a t'il une solution a mon problème ?

       

      Christophe

        • Re: Est-ce possible
          Philippe Grenier

          Bonjour Christophe,

           

          Je sens que vous pourriez aisément retrouver les valeurs que vous désirez en les calculant lors du chargement de vos données grâce à la fonction Previous(), voici un extrait de l'aide en ligne:

           

          previous(expression)

          Returns the value of expression using data from the previous input record. In the first record of an internal table the function will return NULL. The previous function may be nested in order to access records further back. Data are fetched directly from the input source, making it possible to refer also to fields which have not been loaded into QlikView, i.e. even if they have not been stored in its associative database.

          Examples:

          Load *, Sales / previous(Sales) as Increase from ...;

          Load A, previous(previous( A )) as B from ...;

            • Re: Est-ce possible
              Anquetil Christophe

              Bonjour Philippe, merci pour votre réponse.

               

              J'ai donc fait ceci dans mon script avec la fonction peek() plutôt que previous. (en dessous dans l'aide^^)

               

              VentesBase:

              select CART, SUBSTRING(CONVERT(varchar, DATE, 120), 1, 7) as [Période de vente], SUM(QTE) as Unit, SUM(Mtht) as CA, SUM(MTHT-(QTE*PUMP)) as marge,
              SUBSTRING(CONVERT(varchar,DATEADD(Month, -5, DATE),120),1,7) as [Borne mini]
              ,SUBSTRING(CONVERT(varchar, DATE, 120), 1, 7) as [Borne maxi]
              from HistLivraisons2
              where DATE >='01/01/2011' and CART ='018114' and AVOIR=0 and MTHT>0
              group by CART, SUBSTRING(CONVERT(varchar, DATE, 120), 1, 7) ,SUBSTRING(CONVERT(varchar, DATE, 120), 1, 7)
              ,SUBSTRING(CONVERT(varchar,DATEADD(Month, -5, DATE),120),1,7)
              order by  CART, SUBSTRING(CONVERT(varchar, DATE, 120), 1, 7);

              Ventes:
              Load *, peek('Unit',-2)+peek('Unit',-1)+ Unit as cumul1
              Resident VentesBase;
              Drop Table VentesBase;

               

               

              Il en découle une nouvelle contrainte chaque mois (de vente) n'apparait pas systématiquement dans la liste.

               

              Par exemple ventes en janvier mais pas en février, lorsque je fais un peek, je reprend les dernières valeurs dans la table, sans tenir compte de la date, y a t'il un moyen de contourner cette contrainte ?

               

              Christophe

                • Re: Re: Est-ce possible
                  Philippe Grenier

                  Ah oui, effectivement, dans ce cas, vous pourriez construire votre table en y allant par étapes. Voici une approche que vous pourriez suivre, en attachement, un document QlikView avec ce script:

                   

                  DataSet:

                  LOAD * Inline [

                  SalesDate, SalesAmount

                  07/09/2012, 832.36

                  15/09/2012, 156.65

                  10/10/2012, 1215.27

                  16/10/2012, 578.43

                  04/11/2012, 46.47

                  18/11/2012, 345.16

                  04/12/2012, 16.23

                  12/01/2013, 161.18

                  15/01/2013, 116.25

                  29/01/2013, 734.56

                  14/02/2013, 980.67

                  26/02/2013, 87.76

                  26/02/2013, 876.15

                  03/03/2013, 1068.48

                  07/03/2013, 4684.21

                  10/03/2013, 187.87

                  16/03/2013, 1674.24

                  24/03/2013, 894.67

                  24/03/2013, 985.13

                  30/03/2013, 1249.87

                  15/05/2013, 328.75

                  21/05/2013, 2385.76

                  22/05/2013, 87.54

                  08/06/2013, 637.99

                  15/06/2013, 1068.49

                  16/06/2013, 618.54

                  17/07/2013, 319.16

                  18/07/2013, 987.24

                  15/08/2013, 30.89

                  22/08/2013, 423.65

                  08/09/2013, 785.24

                  ];

                   

                  map_Sales:

                  Mapping Load Num(MonthStart(SalesDate)) as SalesMonth, Sum(SalesAmount) as MonthlySales

                  Resident DataSet

                  Group By MonthStart(SalesDate);

                   

                   

                  tmp_MinDate:

                  LOAD Num(Min(MonthStart(SalesDate))) as MinDate

                  Resident DataSet;

                   

                  LET v_MinDate = Peek('MinDate', 0, 'tmp_MinDate');

                  DROP Tables tmp_MinDate, DataSet;

                   

                  MonthlySales:

                  LOAD AddMonths($(v_MinDate), RowNo()-1) as SalesMonth

                     , ApplyMap('map_Sales', Num(AddMonths($(v_MinDate), RowNo()-1)), 0) as MonthlySales

                     , ApplyMap('map_Sales', Num(AddMonths($(v_MinDate), RowNo()-1)), 0)

                     + ApplyMap('map_Sales', Num(AddMonths($(v_MinDate), RowNo()-2)), 0)

                     + ApplyMap('map_Sales', Num(AddMonths($(v_MinDate), RowNo()-3)), 0) as ThreeMonthCumulativeSales

                  AutoGenerate((Year(Today())-Year($(v_MinDate)))*12 + Month(Today())-Month($(v_MinDate)) +1);

                   

                   

                  Bonne journée!

                   

                  /* Petite coquille de code corrigée ;-) */

                    • Re: Re: Re: Est-ce possible
                      Anquetil Christophe

                      Bonjour Philippe,

                      Merci pour votre réponse et votre script.

                       

                      En fait, j'ai une nouvelle problématique, Mapping load ne gère que 2 champs, ici Date et Montant, ce qui correspondait à ma demande initiale, j'ai oublié de dire qu'il fallait tenir compte du code articles (j'ai 12000 articles en base).

                      Dans mon cas j'ai une notion de code article, montant et date. Comment faire pour que tout soit pris en compte ?

                       

                      Article / Période / Montant.

                       


                      Ps: 2 petites erreurs se son glissées dans le script que vous m'avez gentiment fourni, je le précise pour ceux qui en aurait besoin.

                      MonthlySales:

                      LOAD AddMonths($(v_MinDate), RowNo()-1) as SalesMonth // -1 pour prendre en compte le premier mois

                        , ApplyMap('map_Sales', Num(AddMonths($(v_MinDate), RowNo()-1)), 0) as MonthlySales

                        , ApplyMap('map_Sales', Num(AddMonths($(v_MinDate), RowNo()-1)), 0)

                        + ApplyMap('map_Sales', Num(AddMonths($(v_MinDate), RowNo()-2)), 0)

                        + ApplyMap('map_Sales', Num(AddMonths($(v_MinDate), RowNo()-3)), 0) as ThreeMonthCumulativeSales

                      AutoGenerate((Year(Today())-Year($(v_MinDate)))*13 + Month(Today())-Month($(v_MinDate))); // * 13 pour aller jusqu'au dernier mois à cause du décalage en première ligne

                       

                      Cordialement

                      Christophe

                       

                        • Re: Re: Re: Re: Est-ce possible
                          Philippe Grenier

                          Bonjour Christophe,

                           

                          Bien vu pour les coquilles de code, on est jamais à l'abri d'erreurs lorsqu'on se dépêche à fournir une réponse! ;-) Bien que je doute que le *13 soit exact, et qu'il serait peut-être plus judicieux d'ajouter un +1 à la fin du schmilblick pour compenser le décalage. J'ai corrigé mon post original à cet effet.

                           

                          Pour en revenir à votre cas d'affaires, je vous ai fourni un nouveau fichier exemple en attachement qui vous donnera un bon exemple pour gérer de façon similaire la situation en cours. Pour ce faire, j'ai réparti sous 4 codes de produits différents les données de l'exemple précédent, et ai changé le script quelque peu afin de représenter une solution possible à votre requête:

                           

                          DataSet:

                          LOAD * Inline [

                          ProductCode, SalesDate, SalesAmount

                          1, 07/09/2012, 832.36

                          2, 15/09/2012, 156.65

                          3, 10/10/2012, 1215.27

                          4, 16/10/2012, 578.43

                          1, 04/11/2012, 46.47

                          4, 18/11/2012, 345.16

                          1, 04/12/2012, 16.23

                          4, 12/01/2013, 161.18

                          1, 15/01/2013, 116.25

                          2, 29/01/2013, 734.56

                          1, 14/02/2013, 980.67

                          3, 26/02/2013, 87.76

                          1, 26/02/2013, 876.15

                          2, 03/03/2013, 1068.48

                          1, 07/03/2013, 4684.21

                          4, 10/03/2013, 187.87

                          1, 16/03/2013, 1674.24

                          3, 24/03/2013, 894.67

                          2, 24/03/2013, 985.13

                          4, 30/03/2013, 1249.87

                          1, 15/05/2013, 328.75

                          4, 21/05/2013, 2385.76

                          1, 22/05/2013, 87.54

                          3, 08/06/2013, 637.99

                          3, 15/06/2013, 1068.49

                          1, 16/06/2013, 618.54

                          4, 17/07/2013, 319.16

                          1, 18/07/2013, 987.24

                          2, 15/08/2013, 30.89

                          1, 22/08/2013, 423.65

                          3, 08/09/2013, 785.24

                          ];

                           

                          map_Sales:

                          Mapping LOAD ProductCode&'~'&Num(MonthStart(SalesDate)) as ProductSalesMonth, Sum(SalesAmount) as MonthlySales

                          Resident DataSet

                          Group By ProductCode, MonthStart(SalesDate);

                           

                          tmp_MonthlySales:

                          LOAD DISTINCT ProductCode

                          Resident DataSet;

                           

                          tmp_MinDate:

                          LOAD Num(Min(MonthStart(SalesDate))) as MinDate

                          Resident DataSet;

                           

                          LET v_MinDate = Peek('MinDate', 0, 'tmp_MinDate');

                          DROP Tables tmp_MinDate, DataSet;

                           

                          Left Join(tmp_MonthlySales)

                          LOAD AddMonths($(v_MinDate), RowNo()-1) as SalesMonth

                          AutoGenerate((Year(Today())-Year($(v_MinDate)))*12 + Month(Today())-Month($(v_MinDate))+1);

                           

                          MonthlySales:

                          LOAD ProductCode

                             , SalesMonth

                             , ApplyMap('map_Sales', ProductCode&'~'&Num(SalesMonth), 0) as MonthlySales

                             , ApplyMap('map_Sales', ProductCode&'~'&Num(SalesMonth), 0)

                             + ApplyMap('map_Sales', ProductCode&'~'&Num(AddMonths(SalesMonth, -1)), 0)

                             + ApplyMap('map_Sales', ProductCode&'~'&Num(AddMonths(SalesMonth, -2)), 0) as ThreeMonthCumulativeSales

                          Resident tmp_MonthlySales;

                           

                          DROP Table tmp_MonthlySales;

                           

                          Cordialement,

                           

                          Philippe

                            • Re: Est-ce possible

                              Je ne traiterais pas le problème de cette façon. J'ajouterais un axe ANALYSE TEMPORELLE qui permet d'avoir ce cumul glissant sur 6 mois, sur 12 mois, de le comparer à l'anne précédente.

                               

                              Voir document que j'ai créé (en anglais) sur cette question :

                              http://community.qlik.com/docs/DOC-4821

                               

                              Bonne lecture

                              Fabrice

                                • Re: Est-ce possible
                                  Anquetil Christophe

                                  Bonjour Fabrice,

                                  Merci pour votre réponse, j'ai mis un peu de temps à maitriser la technique, mais je crois que j'y suis arrivé.

                                   

                                  J'ai une autre question, à partir du script ci-dessous, je désirerai pouvoir définir un critère basé sur le ROLL6 (qui me servira de dimension) et l'intégrer dans cette même table plutôt que de le calculer dans le graphique.

                                   

                                  Est ce réalisable ?

                                   

                                  Tmp_Achats:
                                  select
                                       CART as [Code article]
                                      ,SUBSTRING(CONVERT(varchar, DATE, 120), 1, 7) as [Periode_Achat]   
                                      ,sum(-QTE) as [Quantité achat]
                                      ,sum(MTHT) as [Montant ligne achat]
                                      ,Count(distinct CPIECE) as [Nombre de commande]
                                     
                                  from HistAchats2
                                  where date >= $(vDateMin) and TYPE = 2
                                  group by CART, SUBSTRING(CONVERT(varchar, DATE, 120), 1, 7);

                                   

                                  Achats:
                                  LOAD
                                      'Achats' as [Type mouvement],
                                      [Code article],
                                      ApplyMap('MapPeriode', Text([Periode_Achat])) as TIME_KEY,
                                      [Quantité achat] as Quantité,
                                      [Montant ligne achat],       
                                      [Nombre de commande]
                                     
                                  resident Tmp_Achats;

                                   

                                  DROP Table Tmp_Achats;

                                   

                                  Merci pour votre aide

                                  Christophe