Discussion :

[metalpouet]

Bonjour à tous,

J'ai de nombreux soucis avec ma dimension temps dans mon TCD. Je l'ai peut-être mal "construite", mais je débute/galère

Dans mon TCD ma dimension temps est en colonne A (je mets l'avancée du fichier en pj).

1 - J'aimerai pouvoir afficher toutes les journées et pas celle uniquement avec des données.
Dans les champs, j'ai tenter la validation du paramètre du champ "Afficher les éléments sans données", mais j'obtiens tous les jours à chaque groupe de semaine/mois.



2 - Je n'arrive pas à faire de forme conditionnelle sur ces champs. J'aimerais faire ressortir les week-end. J'ai tenté = OU(JOURSEM($A$A)=1;JOURSEM($A$A)=7) sans succès.
Dans l'idéal j'aimerais faire ressortir les lignes des we, mais vu mes tentatives sur les autres champs, j'ai l'impression que c'est limité à la cellule.

3 - J'ai besoin de faire un cumul par semaine de la colonne F (Amplitude jour). Etant donné que c'est un maximum du feuillet "calcul extract", je n'ai même l'once d'une piste.

Si vous avez des réponses/conseils, ce serait top !

[Raccourcix]

Bonjour

je vous proposais hier de travailler avec Power Pivot. Cela vous permettrai d'avoir une table de date avec tous les jours de l'année, les n° de semaine (en gérant la fin/début d'année), le trimestre, le mois
mais également les mesures : premier, dernier, écart
la table de date est reliée à votre table sur le champ "Dates envois"

voir en PJ. pas de souci pour avoir tous les jours de l'année, pour gérer les WE...

pour la semaine :

Code :

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

=ANNEE([@Date]-JOURSEM([@Date];3)+3)&" S"&TEXTE(NO.SEMAINE.ISO([@Date]);"00")

sinon vous aurez des problèmes, regardez le 30 décembre 2019 il est en semaine 1 mais de 2020


pour les mesures DAX en gérant 1h de moins si + de 4 heures d'écart

Code :

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

1
2
3
4
5
Premier:=MIN(Tableau4[Heures envois])
Dernier:=MAX(Tableau4[Heures envois])
Amplitude:=IF(ISBLANK([Premier]);"";[Dernier]-[Premier]-IF(([Dernier]-[Premier])>TIME(4;0;0);TIME(1;0;0);0))
Nb:=COUNT([Heures envois])
Transfert:=SUM(Tableau4[fwd/re])

Stéphane

[metalpouet]

Merci beaucoup pour la réponse. Ca avance :)

Cependant j'ai encore quelques questions suite à ma tentative de reproduction de ce que vous avez fait.

1-J'ai réussi à mettre les mois en texte, mais j'ai plus de difficulté pour les dates (au format vendredi 13 mai par exemple). J'ai fait la modification à tous les niveaux (extract, dans la table de power pivot...). Mais ça reste au format jj/mm/yyyy. Je peux faire une modif cellule par cellule (ou alors sur toute la colonne mais j'ai des incohérences avec les années) mais je ne vois pas comment faire en sorte d'avoir le bon format.

2-Vos valeurs (nb mail, nb transfert) ne sont pas affichées lorsqu'elles à 0. C'est parfait, mais je ne vois pas comment faire (à part avec mon ancienne méthode d'affichage conditionnel). J'ai retravaillé le fichier, un moment ajouté le calcul dans la table power pivot, avant de revenir à un fonctionnement comme le votre, mais j'ai des 0 qui s'affichent pour le nb.transfert.

3-La table date peut-elle est "paramétrée" afin de n'afficher que les dates de l'extract ? Par exemple, si j'ai un nouvel export avec des dates pré-2018, je dois la maj en fonction ?

4-Comment faire pour obtenir un sous-total de l'amplitude par semaine? Lorsque je mets les sous-totaux j'ai des résultats étranges.

Merci pour votre aide passée et celle à venir j'espère !

[Raccourcix]

Bonsoir

On peut également envisager une solution sans Power Pivot avec uniquement des calculs dans la table de dates : SOMME.SI.ENS, NB.SI.ENS, MIN.SI.ENS, MAX.SI.ENS et l'écart entre le MAX et le MIN =[@Max]-[@Min]-(([@Max]-[@Min])>4/24)/24 pour retirer une heure si écart > 4 heures


Le TCD sera basé sur cette table. le calcul de la somme des écarts par semaine et par mois sera facilité (au format [h]:mm:ss)

Stéphane

[Raccourcix]

Nos messages sont presque simultanés
pour éviter les 0, pensez au format personnalisé 0;-0;pour les heures au delà de 24h : [h]:mm:ss;;pour les dates : faites commencer la table de dates = MIN(dates de votre export)

Stéphane

[metalpouet]

Nos messages sont presque simultanés
pour éviter les 0, pensez au format personnalisé 0;-0;pour les heures au delà de 24h : [h]:mm:ss;;pour les dates : faites commencer la table de dates = MIN(dates de votre export)

Stéphane

Merci pour les 0 (et le reste)!!!

Pour les heures au delà de 24h, vous évoquez cela de manière générale ou bien en rapport à ma remarque sur les sous-totaux de l'amplitude? Parce que c'est déjà à ce format, mais avec des valeurs incongrues.
C'est bon pour nb.mail et nb.transfert, pas pour premier et dernier mail mais ça n'a pas de sens de toute façon (si vous avez déjà une idée de comment les supprimer), et pour amplitude; je ne vois pas



Pour la table de dates, merci pour l'idée, mais il faudra qu'elle se créer automatiquement, mais je verrais ça plus tard.

Une idée pour mettre les dates en libellés?

Bonne soirée

[metalpouet]

Bonsoir

On peut également envisager une solution sans Power Pivot avec uniquement des calculs dans la table de dates : SOMME.SI.ENS, NB.SI.ENS, MIN.SI.ENS, MAX.SI.ENS et l'écart entre le MAX et le MIN =[@Max]-[@Min]-(([@Max]-[@Min])>4/24)/24 pour retirer une heure si écart > 4 heures


Le TCD sera basé sur cette table. le calcul de la somme des écarts par semaine et par mois sera facilité (au format [h]:mm:ss)

Stéphane

Je ne suis pas sur de comprendre. Je n'utiliserai plus Power Pivot, donc le lien entre la table Date et ma table Extract (ou Tableau) ?
Je devrais mettre les données de la table Extract dans d'autres colonnes de la table Date?

Si c'est ça, alors faudra vraiment que la table Date se construise automatiquement (avec des min et max de la table Extract comme vous l'évoquiez), sinon ça fera du travail manuel à chaque fois.

Bref, je fatigue. Je reprendrais demain ;)

[Raccourcix]

Bonjour
Oui, mon autre proposition se fait sans Power Pivot. Avec un table de dates manuelle on peut quand même prévoir une plage suffisant large.
Sinon pour les sous-totaux par semaine ou par mois c'est possible en DAX. Voir sur le forum Power BI s'il n'y a pas des questions similaires.
Pour l'instant c'est le WE et mon Excel est en repos pour 2 jours
Bon courage pour votre projet
Stéphane

[Raccourcix]

Bonjour
avec Power Pivot, pour ne pas afficher le min et le max dans les sous-totaux vous pouvez utiliser HASONEVALUE en ajoutant une nouvelle mesure : IF(HASONEVALUE(Dates[Date]);[Premier];"")dans les sous-totaux il y a plusieurs valeurs de dates, donc ça affichera ""

pour les sous-totaux corrects au niveau hebdomadaire ou mensuel, il faut utiliser SUMX
Amplitude_bis:=SUMX(Dates;IF(ISBLANK([Premier]);"";[Dernier]-[Premier]-IF(([Dernier]-[Premier])>TIME(4;0;0);TIME(1;0;0);0)))
Stéphane

[metalpouet]

Incroyable. Merci beaucoup Stéphane ! C'est exactement ça.
Je n'aurais jamais trouvé je pense...
J'étais reparti sans Power Pivot mais non seulement je galèrerais encore, mais surtout je trouvais ça dommage ne pas l'utiliser.

Il ne reste plus qu'à trouver comment faire pour afficher la date au format jj/mm/yyyy pour faire une première version viable (et quelques retouches sur la macro vba d'extract mais j'arrive au bout).
Concernant le format de date, dans le TCD je ne vois que le format pour la cellule et pas tout le champ. Dans Power Pivot et dans l'extract c'est au bon format.
Si vous avez un piste ou solution, ça serait top

[Raccourcix]

...(et quelques retouches sur la macro vba d'extract mais j'arrive au bout)

Je trouve dommage d'utiliser VBA pour extraire les données, Power Query est fait pour cela.

Power Query et Power Pivot sont complémentaires : le premier permet d'extraire et de transformer les données pour les charger dans le modèle de données Power Pivot dans lequel on défini les mesure pour faire les analyses avec des TCD
La bascule dans Power BI est alors immédiate : il suffira d'importer vos requêtes et mesures depuis votre fichier Excel.
Personnellement j'ai divisé par plus de 10 mon utilisation de VBA depuis que je me suis mis à ces outils "Power"
Stéphane

[metalpouet]

l'extract n'étant pas la partie la plus compliquée et ne connaissant pas les outils Power, je suis parti sur du "classique" ;)

[Raccourcix]

Bonsoir

Depuis la version 2016, le "classique" pour l'extraction des données devrait être Power Query.
Cet outil existait auparavant mais en Add-in et ce n'était pas toujours possible de le mettre en œuvre (partage de fichier, politique d'entreprise)
mais depuis cette version, c'est tellement plus simple avec PQ qu'il me semble obligatoire de travailler avec.
Power Pivot sous Excel est plus complexe d'approche mais vous avez constaté que ce n'est pas si difficile que ça !


Sinon pour le format des dates : avez-vous dissocié ce champ ?
Personnellement j'ai désactivé le regroupement automatique des dates dans les options.

Stéphane

[metalpouet]

Bonsoir,

Concernant PQ, j'ai tenté l'extract de mon PST en suivant la procédure suivante :
https://www.xelplus.com/import-outlo...h-power-query/

Mais j'ai les messages suivants :



Je ne suis même pas certain que PQ soit "nativement" opérationnel sur mon poste.


Mais oui, j'ai vu des vidéos qui donnaient envie, mais je n'ai ayant une solution qui "fonctionne" je n'ai pas priorisé ce sujet à ma todo.


Concernant les dates, j'ai bien dissocié ce champ


Je remets le fichier si besoin

Xavier

[Raccourcix]

Bonjour
je n'ai jamais utilisé le connecteur pour lire directement dans Outlook. je ne saurais dire pourquoi cela ne fonctionne pas.
depuis la version 2016, PQ est dans le menu "Données" : récupérer et transformer

sinon les dates me semblent au format souhaité.

Stéphane

[metalpouet]

Bonjour,

Pour outlook je vais tenter d'un autre poste dans la semaine. En tout cas, ça marche sur d'autres sources (j'ai tenté d'une source excel).
Pour les dates, le format désiré dans le TCD est "Lundi 16 Mai 2022" (ce qui est le cas dans tous les autres onglets) et pas "16/05/2022" comme actuellement.

Xavier

[Raccourcix]

Et en appliquant la mise en forme à toutes les cellules dates


Stéphane

[metalpouet]

Merci, mais c'est bien fait par une sélection manuelle des cellules jours?
Si nous prenons tout, ça change le format des années.
Ma solution est donc de tout sélectionner (sinon il y a plus de 1000 lignes) et de remettre les années au bon format. Ca fait un peu bidouille manuelle ;)

En tout cas, encore merci Stéphane. J'ai pu présenter un truc qui se tient. Il reste beaucoup à faire, pour qu'il soit réutilisable, automatisable et paramétrable, mais j'apprends ;)