Discussion :

[Arno21]

Bonjour à tous;

j'ai arpenté le forum et trouvé quelques pistes dont le code de tofalu puis les adaptations de ce code mais au bout de 2 jours rien n'y fait, je sèche et j'en appelle à votre précieuse contribution pour réaliser mon calcul de médiane

Je dois calculer des valeurs medianes en regroupant sur un champ "année" dans une même table.
Ex : valeur mediane des chiffres d'affaires en 2010. Puis valeur mediane de ce même champ sur 2011.
Je devrais ensuite realiser le même calcul sur le champ marge brute , etc, etc..

Puis dans un second temps je regrouperais sur l'année ET sur une typologie client, ce qui fera un second critère à intégrer

La ligne de code de tofalu ma permis de calculer la mediane chiffre d'affaires sur toute la table mais je ne parviens décidément pas à intégrer cette notion de regroupement par année ou autre....

S'il vous faut un echantillon de ma bdd , dites moi.

merci beaucoup d'avance

[ClaudeLELOUP]

Bonjour,

Peux-tu livrer un échantilon dans la db jointe sans modifier l'extension (Access2000)

La ligne de code de tofalu ma permis de calculer la mediane

Donne les références.

[Arno21]

Bonsoir Claude !

La table "TOUS RATIOS"
Le champ de calcul de la mediane : "R01_CHIFFRE D'AFFAIRES HT" (pui je le declinerais sur les autres champs R_02, etc...)
Le regroupement : "Année N bilan"

puis ensuite même chose en ajoutant critère de regroupement "groupe regional"
(ce champ sera integralement renseigné dans le futur)

Merci d'avance

[ClaudeLELOUP]

Que veux-tu comme résultat ? Uniquement la médiane ?

On pourrait concevoir un formulaire avec
- une liste déroulante qui donne le nom des colonnes susceptibles d'un calcul de médiane : R01_CHIFFRE D'AFFAIRES HT, R_02, etc. ;
- une liste déroulante pour les années (+ toutes) ;
- une liste déroulante pour les groupes (+ tous).

L’utilisateur choisit un item dans chaque liste et le formulaire affiche alors la médiane.

Ça te convient ?

Tu n’as pas donné les références du code de Tofalu.

[Arno21]

Bonjour

je souhaite faire tous les calculs au sein d'une requete.

Je me servirais des résultats dans un état récapitulant les medianes des différentes valeurs

concernant le code de Tofalu c'est dans cette discussion et c'est d'ailleurs vous qui l'aviez adapté !


http://www.developpez.net/forums/d13...diane-requete/


merci encore

[ClaudeLELOUP]

Bonjour,

Montre, à l'aide d'un dessin de ce que tu voudrais comme état pour présenter le résultat.

[Arno21]

Bonjour

En fait l'etat existe déjà car les données etaient traitées via Excel avant mais je n'ai pas la main sur la structure je réécris donc la totalité de l'application.

Ci joint l'etat existant

En complément je vais aussi avoir besoin de calculer les quartiles pour les intégrer à cet état...

merci d'avance

Bien cordialement

Arno

[ClaudeLELOUP]

Bonjour,

J’avais pensé à une fonction « couteau suisse » dans laquelle tu aurais renseigné le nom de la table et le nom de la colonne et qui rendrait d’un coup Max, Min, Moyenne, Médiane et Q1 à Q4, mais comme ton état présente de nombreux niveaux d’agrégation, ça risque de durer.
Combien y a-t-il de lignes dans ta table ?

[Arno21]

Bonjour

la table comporte actuellement 1470 enregistrements donc pas violent
Elle va s'etoffer tous les ans d'environ 300 nouveaux enregistrements

J'ai vraiment besoin de calculer dans une ou plusieurs requetes pour reprendre les infos dans des états customisés ensuite en fonction du destinataire (droits d'accès différents en fonction du poste occupé , etc)

Si dejà je pouvais avoir le calcul de la mediane regroupé par année , ce serait top
L'aggregation par année + par groupe regional ensuite
puis aggregat par année + par tranche Chiffres affaires

avec ça je suis le roi du pétrole !

merci encore

[ClaudeLELOUP]

J’essaie de comprendre la relation entre la table que tu donnes dans le billet 3 et le tableau que tu montres dans le billet 7.

Pour les lignes du tableau
Je m’attendais à trouver dans la table des colonnes en relation avec chaque ligne de ton tableau.
Je vois cette relation pour
CA HT => R01_CHIFFRE D'AFFAIRES HT
Marge brute => R02_MB GLOBALE
…
Mais pas pour toutes les lignes.
1. Tu me confirmes que dans ta table complète, tu as bien ce genre de relation pour chaque ligne du canevas de tableau.


Pour les blocs de données (Individu, National, Polyculture…)
2. Je suppose que « Individu » correspond à « concess ».
3. Comment doit-on agréger pour obtenir National ? La somme de tous les « concess » ?
4. Comment doit-on agréger pour obtenir Polyculture ? La somme de tous les « concess » qui ont un « groupe regional » spécifique ?
5. Je suppose que le tableau que tu montres est celui d’une année particulière.

Donne tes réponses en utilisant les N°, pour que ce soit clair.

[Arno21]

Effectivement je me rends compte que je n'ai pas été très précis :

J'essaie de répondre à vos questions :

[I]Pour les lignes du tableau
Je m’attendais à trouver dans la table des colonnes en relation avec chaque ligne de ton tableau.
Je vois cette relation pour
CA HT => R01_CHIFFRE D'AFFAIRES HT
Marge brute => R02_MB GLOBALE
…
Mais pas pour toutes les lignes.
1. Tu me confirmes que dans ta table complète, tu as bien ce genre de relation pour chaque ligne du canevas de tableau.
Oui en fait je n'ai fourni qu'un extrait de la table qui alimenterait uniquement le premier pavé de l'état (SOLDES INTERMEDIAIRES DE GESTION)
Je te fournis la table complete mais infos confidentielles STP

Pour les blocs de données (Individu, National, Polyculture…)
2. Je suppose que « Individu » correspond à « concess ». OUI
3. Comment doit-on agréger pour obtenir National ? La somme de tous les « concess » ? OUI TOUS LES CONCESS SUR UNE MËME ANNEE
4. Comment doit-on agréger pour obtenir Polyculture ? La somme de tous les « concess » qui ont un « groupe regional » spécifique ? OUI IL EXISTE UNE TABLE DES GROUPES REGIONAUX il y en a 10. Egalement dans la base mdb ci jointe
5. Je suppose que le tableau que tu montres est celui d’une année particulière. OUI CEST LE TABLEAU DE 2009 TE CHAQUE FOIS QUE L'ON SORTIRA L'ETAT CE SERA EN SELECTIONNANT UNE ANNEE DONNEE (bien souvent N-1) MAIS PAS IMPOSSIBLE DE VOULOIR SORTIR N-2 , N-3 et N-4 POUR SUIVRE LES EVOLUTIONS. CE CHOIX SE FERA AU TRAVERS D'UN FORMULAIRE



MERCI BEAUCOUP

[ClaudeLELOUP]

Le canevas est-il toujours le même ?
Bloc individu | Bloc National | Bloc du secteur de l’individu

[Arno21]

aux dernières nouvelles oui ;-))

mon nouvel état sera identique a celui fourni ; j'ajouterais tres certainement les quartiles

En fait je n'ai aucun soucis pour batir l'etat et paramétrer en fonction d'un formulaire : choix concess + choix année

Ce sont vraiment les calculs qui me posent pb (hormis MIN / MAX / MOYENNE of course)

[ClaudeLELOUP]

Je pense à un état standard avec des champs vierges.
Un formulaire pour donner les paramètres année et individu de l’état souhaité.
On garnirait les champs vierges à la volée avec une fonction qui calculerait pour chaque ligne les Max, Min, Moyenne, Médiane et Q1 à Q4 de chacun des blocs.
Comme pour une année donnée, il n’y a que quelque 300 lignes à traiter, le délai de réponse devrait être quasi instantané.
Un chouette challenge, il me semble.
Qu’en penses-tu ?

[Arno21]

ca sent fort bon !!

il faut juste que je soi capable de faire evoluer au cas ou un nouveau type de regroupement est demandé...
mais ca me plait !!

[ClaudeLELOUP]

OK, je pense à ça.

Je te reviens quand j'ai quelque chose à montrer.

Un peu de patience, c'est quand même un gros morceau !

[Arno21]

OK Claude, ca ferait un cadeau de Noel sympa et 3 semaines c'est un peu le timing dont je dispose....

merci et bon w-e

[ClaudeLELOUP]

J'avance. J'aurai sans doute quelque chose d'ici demain soir.

[ClaudeLELOUP]

Voici une proposition.

Un formulaire permet de choisir une entreprise



un clic et vient alors cet état :




L’idée est celle-ci

- à part les intitulés des lignes, tous les contrôles sont indépendants : ils seront complétés à la volée (événement Au formatage de la section Détail) au fur et à mesure qu’Access construit chaque ligne de l’état ;
- la source est une table qui contient les libellés des lignes, le titre de regroupement et le nom de la colonne de données dans la table Tous Ratios




La table tCanevas ne sert qu’à amorcer la création d’une ligne de l’état.

Description du code du formulaire d’appel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Option Compare Database
Option Explicit
 
Private Sub btEtat_Click()
  If IsNull(Me.cboConcessionnaire) Or IsNull(Me.cboAnnee) Then
      MsgBox "Année et concessionnaire doivent être spécifiés", vbCritical
      Exit Sub
    Else
     'Habillage entête de l'état
     Application.Echo False 'pour éviter 'éclair à l'écran lors de la manipulation
     DoCmd.OpenReport "eRatios", acViewDesign
     Reports!eRatios.txtIndividu.ControlSource = "=""" & Me.cboConcessionnaire & """"
     Reports!eRatios.txtGroupe.ControlSource = "=""" & Me.txtGroupe & """"
     Reports!eRatios.txtAnnee.ControlSource = "=""" & Me.cboAnnee & """"
     DoCmd.Close acReport, "eRatios", acSaveYes
     DoCmd.OpenReport "eRatios", acViewPreview
  End If
  Application.Echo True
End Sub

Description du code de l’événement Au formatage (section Détail)

Pour les données de l’entreprise, on utilise une fonction de domaine pour consulter la tableTous Ratios

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
'---------------------
  'Données de l'individu
  '---------------------
 
  Me.valIndividu = DLookup("[" & Me.txtCanCode & "]", "[TOUS RATIOS]", "" _
                     & "concess=""" & Me.txtIndividu & """ AND [Année N bilan]=" & Me.txtAnnee)

Pour les données du Groupe, on se créer une table tEchantillon



qui ne comporte qu’une seule colonne avec les données relatives au groupe, classées par ordre croissant.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
'-----------------
  'Données du Groupe
  '-----------------
 
  'Création tEchantillon
  DoCmd.RunSQL "SELECT [" & Me.txtCanCode & "] AS Donnees  INTO tEchantillon " _
                                    & "FROM [TOUS RATIOS] " _
                                    & "WHERE [Année N bilan] = [Forms]![fSelection]![cboAnnee] " _
                 & "And [groupe regional] = [Forms]![fSelection]![txtCodeGroupe] " _
                 & "ORDER BY [" & Me.txtCanCode & "];"

On crée un RecordSet avec cette table pour pouvoir y naviguer et ainsi déterminer :
le minimum, la moyenne, le maximum, la médiane, les 1er et 3e quartiles.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
  'Création du record set
  Set rst = CurrentDb.OpenRecordset("tEchantillon")
   'Minimum
   Me.GrMin = rst.Fields(0)
   'Moyenne
   Me.GrMoy = 0
   Do Until rst.EOF
     Me.GrMoy = Me.GrMoy + rst.Fields(0)
     rst.MoveNext
   Loop
   Me.GrMoy = Me.GrMoy / rst.RecordCount
   'Maximum
   rst.MoveLast
   Me.GrMax = rst.Fields(0)
   'Médiane
      rst.PercentPosition = 50 'on se positionne au milieu
                             'de deux choses l'une :
                             ' - le nombre est pair => on est situé juste sur la médiane ;
                             ' - le nombre est impair => on est au début de la deuxième moitié,
                             'la médiane, c'est la moyenne de cet enregistrement et celui qui précède
   Me.GrMed = rst.Fields(0) 'c'est la médiane si le nbre est impair
   If rst.RecordCount Mod 2 = 0 Then ' il est pair
       rst.MovePrevious 'on recule de 1
       Me.GrMed = Me.GrMed + rst.Fields(0) 'on rajoute le précédent
       Me.GrMed = Me.GrMed / 2                   'et on divise le total par 2
   End If
   'Q1, Q3
   rst.PercentPosition = 25
   If rst.RecordCount Mod 4 = 0 Then rst.MovePrevious
   Me.GrQ1 = rst.Fields(0)
   rst.PercentPosition = 75
   If rst.RecordCount Mod 4 = 0 Then rst.MovePrevious
   Me.GrQ3 = rst.Fields(0)

Pour l’ensemble national, on procède mutatis mutandis :

la création de table est cette fois :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
DoCmd.RunSQL "SELECT [" & Me.txtCanCode & "] AS Donnees  INTO tEchantillon " _
                                    & "FROM [TOUS RATIOS] " _
                                    & "WHERE [Année N bilan] = [Forms]![fSelection]![cboAnnee] " _
                                    & "ORDER BY [" & Me.txtCanCode & "];"

et le nom des contrôles est chaque fois adapté.

La bdd de test en pièce jointe.

N.B. J’ai gardé les noms de fichiers et colonnes de tes tables tels quels pour que tu puisses directement te servir du programme.
Tu aurais intérêt à éviter à l’avenir des nommages avec des lettres accentuées et des espaces (ça alourdit considérablement la syntaxe). À l’occasion lit ceci : Descriptif des conventions typographiques du code Visual Basic de Jean-Philippe AMBROSINO.

[Arno21]

Bonjour
je n'ai pas encore regardé dans le détail mais déjà je suis impressionné ..
Je regarde et reviens vers vous mais ça sent plus que bon ...

A très vite