Diviser un volume compris entre 2 dates en volume trimestriels
Bonjour à tous,
Je cherche à obtenir les volumes de ventes prévisionnelles par année.
Je fais une analyse sur les volumes de produits demandés sur un marché (équipement médical).
Pour cela, j'ai une date de début d'appel d'offre et une date de fin, avec un volume.
Exemple 1: En gros, l’hôpital demande pour 3 ans entre le 01/04/2016 et 01/04/2018 par exemple, un volume total sur la durée de marché de de 100 compresses.
Exemple 2: Un autre hopital va demandé le même produit, entre le 01/06/2016 et le 01/01/2019, pour un volume de 500 compresses sur la durée totale du marché.
J'ai un TDC qui me permet d'obtenir des résultats sur ces marchés mais j'ai un problème au niveau des dates.
C'est une analyse sur 10 ans et j'aimerais pouvoir avoir les volumes de ventes prévisionnelles de chaque produit par an, voir par trimestre idéalement, non pas par date de début et de fin de marché.
Seulement, les dates de mon TDC sont les dates de début et de fin de marché, donc si je divise les volumes par an par exemple, j’obtiens seulement les volumes prévisionnels entre le 01/04/2016 et 01/04/2017 (exemple 1)
J'aimerais pouvoir avoir la somme annuelle de volume de compresses. Ca demande donc je suppose de diviser les volumes à un moment donné.
Si dans mon TDC je cherche les volumes sur l'année 2017, j'aimerais avoir les volumes de l'exemple 1 diviser entre le 01/04/2016 et le 31/12/2016 et les volumes de l'exemple 2 divisé entre le 01/06/2016 et le 31/12/2016
Je ne peux pas communiquer le tableau excel car il est confidentiel mais j'ai créé un autre tableau pour illustrer la base
PS: Je n'ai pas les capacités d'utiliser VBA ou ACCESS
C'est exactement ce qu'il me faut c'est incroyable ! Le problème c'est que j'ai microsoft business et non microsoft plus, donc pas powerquery. Impossible sans ça ?
Si je trouve un moyen d'obtenir cette extension excel, tu as eu besoin de créer d'autres champs dans la matrice ou directement sur le tcd ?
Encore merci
Je n'arrive pas à voir la formule que tu as pour les nouveaux champs calculés : année, années, date, qté/jour.
Qté par jour je vais trouver mais date tu as fait comment ?
Merci pour ta réponse,
J'ai regardé ton fichier et j'ai compris à quoi ressemblait power query, je l'ai sur mon excel.
C'est en fait des requêtes que tu as créé et pas des champs calculés simples et bêtes dans le TCD.
Je ne sais pas pourquoi mais ton classeur est comme "protégé", je ne sais pas, mais je n'arrive pas à comprendre les sources de tes calculs dans les requêtes, ou les étapes apliquées.
Je t'avoue que je n'ai jamais utilisé ça, ça à l'air génial mais j'ai un peu du mal à appréhender le système.
Fx-Calendrier, c'est un autre type de requête on dirait, t'as fait comment pour le créer ? On dirait du VBA
La requête fonction appelée, toutes les nouvelles colonnes, trimestre, année, qté/jour, la source c'est un tableau excel que tu as fait ?
J'essaye de reproduire ton schéma sur l'ensemble de mes données
RE
J'ai simplifié un peu.
On a une fonction fx_Calendrier capable de créer la liste de tous les jours entre 2 dates indiquées.
J'ai enlevé tout sauf les dates : c'est plus simple d’autant qu'on n'utilise pas le reste
On charge le tableau de tes marchés dans PQ
On a une requête qui
- utilise la fonction fx_Calendrier pour lister du 1er jan 2016 au 31 Déc 2020
- on ajoute en colonne personnalisée la requête marchés ce qui crée un produit cartésien marchés - dates
- on formate les dates début et fin marché en date et non date/heure (obtenu par défaut)
- on ajoute une colonne pour marquer toutes les dates non comprises entre début et fin de marché de chaque ligne puis on filtre et on supprime la colonne
- on ajoute une colonne personnalisée Volume /an divisé par 365
On ferme PQ et on charge juste la connexion
Reste plus qu'à faire le TCD sur cette requête en créant un regroupement sur les dates comme tout TCD le permet
Tu peux dans l'interface PQ voir chaque étape dans le volet de droite : les roues dentées permettent de voir les détails (double clic sur le rouage)
PQ utilise le langage M
Okay merci je m'y suis mise et je commence à comprendre
Par contre il y a quelque chose que je ne comprend pas dans la requête fonction appelée.
Dans l'éditeur avancé, on voit que tu avais demandé d'afficher les prix chaque jour entre le 01/01/2016 et le 31/12/2020. Pourtant, les données ne vont que jusqu'au 01/01/2019. Comment faire ? J'ai essayer de modifier le codage pour faire débuter en 2009 et faire arrêter en 2030, mais ça ne change pas même quand j'actualise manuellement
Je me suis mal exprimée,
Evidemment ça s'arrête à cette date car je ne t'ai donné que 2 lignes.
Mais là j'en ai 3100 environ et j'ai peur que ça fasse énorme (3100*365*20 ans d'étude environ)
Re
En principe, PQ ne charge pas tout en mémoire donc cela permet de gérer de gros volumes.
Néanmoins pour le TCD il y a des limites donc à voir...
Il y a toujours moyen de filtrer dans PQ pour limiter à 10 ans ou autre car il n'est pas certain que 20 ans soit une bonne maille d'analyse... mais c'est ton univers pas le mien donc je peux me tromper...
Salut ! Merci pour ces précieux conseils, j'ai réussi à faire ce que je voulais, presque !
Dernière petite question et après j'arrête promis
Comment as tu obtenu les champs AnnéeS et TrimestreS dans ton TCD, je ne les vois pas en champs calculés et ces champs n'apparaissent pas dans les requêtes
Camille
Ah non c'est simplement un regroupement de champs excuse moi
Et bien merci encore pour tout !
Re,
Est-il possible de coder en M pour obtenir une ligne excel pour le prix de la gamme par mois durant X année (janbier2009, février 2015...) plutôt que par jour ? Parce que ça sors beaucoup trop de données en la ramenant à une unité journalière
Merci
RE
Il faut modifier le calendrier pour rintégrer le mois et l'année puis enlever les doublons. (Ou bien faire une liste des mois des années concernées)
Pour les marchés extraire année et mois de début et de fin et croiser sur cette base.
J'ai ajusté la fin à moins 1 jour, soit au mois précédent si les dates de fin sont toujours un 1er du mois...
Super merci ! C'est carrément mieux maintenant !
Par contre j'ai un autre soucis, dans le véritable tableau, j'ai également le concurrent attributaire du marché de temps en temps et des informations sur le montant retenu, mais pas toujours, donc beaucoup de ZERO
Dans mon TCD, quand je veux obtenir le prix moyen sur un produit concurrent, mes résultats sont faux car le TCD prends évidemment les zéro en compte. Tu penses que je peux y remédier avec une requête ?
Voici l'exemple complet en PJ
Bonjour
En filtrant les prix moyen à 0 juste après intégration des marchés dans PQ...
Salut,
Oui mais si je fais ça, PQ retire les lignes dont le prix moyen concurrent est nul ?
Et j'ai besoin de toutes mes lignes car le début m'intéresse pour évaluer l'ensemble des besoins sur le marché
Bonjour
On veut bien aider mais tu files 8 lignes sans aucune explication ni des colonnes concernées ni du calcul final...
Ma boule de cristal étant en vacances...
Le fichier est trop lourd c'est pour ça que je ne te l'ai pas envoyé avant
Tu devrais pouvoir le télécharger par ce lien, dis moi si c'est bon
RE
fichier introuvable...
Donne plutôt des explications en commentant tes 8 lignes...