topic Re: Est-ce possible in Francophones

Est-ce possible

chris987 — Thu, 12 Sep 2013 12:50:09 GMT

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).

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

pgrenier — Thu, 12 Sep 2013 12:55:23 GMT

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

chris987 — Thu, 12 Sep 2013 13:55:40 GMT

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

pgrenier — Fri, 13 Sep 2013 12:33:59 GMT

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

chris987 — Mon, 16 Sep 2013 13:28:38 GMT

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

pgrenier — Mon, 16 Sep 2013 23:02:23 GMT

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

Wed, 18 Sep 2013 12:23:03 GMT

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

chris987 — Tue, 24 Sep 2013 14:44:16 GMT

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