Discussion :

[Pierre Dumas]

Bonjour

Cela fait quelques temps que je cherche, mais je n'y arrive pas.

Données de départ :
J'ai quatre colonnes :
departement (01, 02, ..., 19, 21, 29, 2A, 2B, 30, ..., 94, 95, 971, 972, ..., 976)
sexe (1, 2)
date (18/03/2020, 19/03/2020, ..., 19/05/2021)
valeur (-14, -13, ..., 50, 51)

Toutes les combinaisons des trois premières colonnes existent (à chaque département, pour chaque sexe et chaque date, il y a une valeur).

Si besoin d'un jeu de données, prendre celui, public, sur les données hospitalières COVID.

Objectif :
Je cherche à réaliser la moyenne mobile sur 7 jours glissants, par département et par sexe.
Ceci afin de pouvoir tracer un graphique et voir l'évolution de cette valeur au cours du temps.

Si quelqu'un a une idée, je suis preneur

Bonne fin d'après-midi

Pierre Dumas

[Raccourcix]

Bonjour Pierre

une proposition avec Power Pivot

1. vous chargez vos données dans le modèle de données
2. vous ajoutez une colonne calculée

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
=VAR Departement = Tableau1[dep]
VAR Sexe = Tableau1[sexe]
VAR Jour = Tableau1[jour]
RETURN AVERAGEX(
	FILTER(
		Tableau1;
		Tableau1[dep]=Departement&&
		Tableau1[sexe]=Sexe&&
		Tableau1[jour]>=(Jour-7)&&
		Tableau1[jour]<=Jour
		);
	Tableau1[hosp]
	)

Résultat dans Excel avec 2 segments sur le département et le sexe


Cordialement
Stéphane

[Raccourcix]

Re-

Avec une solution Power Query

1. ajout d'un colonne avec la liste {0..7}
2. développement de la liste en ligne (donc multiplication par 8 de la table )
3. ajout d'une colonne avec Date.AddDays et moins le décalage de 0 à 7
4. Fusion de la table avec elle-même une fois sur le département + le sexe + les dates de départ d'un coté et sur les dates décalées de l'autre
5. récupération de l'indicateur sur 7 jours
6. regroupement sur le département + le sexe + les dates de départ en faisant la moyenne des valeurs remontées à l'étape précédente

C'est beaucoup pour lourd et lent qu'avec Pivot mais le résultat est le même (ouf !)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"dep", type text}, {"sexe", Int64.Type}, {"jour", type date}, {"hosp", Int64.Type}, {"rea", Int64.Type}, {"HospConv", type text}, {"SSR_USLD", type text}, {"autres", type text}, {"rad", Int64.Type}, {"dc", Int64.Type}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "Décalage", each {0..7}),
    #"Décalage développé" = Table.ExpandListColumn(#"Personnalisée ajoutée", "Décalage"),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Décalage développé", "Date_décalée", each Date.AddDays([jour],-[Décalage])),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Personnalisée ajoutée1", {"dep", "sexe", "Date_décalée"}, #"Personnalisée ajoutée1", {"dep", "sexe", "jour"}, "Personnalisée ajoutée1", JoinKind.LeftOuter),
    #"Personnalisée ajoutée1 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Personnalisée ajoutée1", {"jour", "hosp"}, {"Personnalisée ajoutée1.jour", "Personnalisée ajoutée1.hosp"}),
    #"Lignes groupées" = Table.Group(#"Personnalisée ajoutée1 développé", {"dep", "sexe", "jour", "hosp"}, {{"Moyenne", each List.Average([Personnalisée ajoutée1.hosp]), type nullable number}})
in
    #"Lignes groupées"

Stéphane

[Raccourcix]

Encore moi avec une mesure DAX :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
Moyenne mobile:=
VAR Date_Max=MAX(Tableau1[jour])
VAR Date_Min=MIN(Tableau1[jour])-7
RETURN 
	CALCULATE(
		AVERAGE([hosp]);
		Tableau1[jour]<=Date_Max&&Tableau1[jour]>=Date_Min
		)
	*DISTINCTCOUNT([dep])
	*DISTINCTCOUNT([sexe])

Je calcule la moyenne entre la date min de la période - 7 et la date max que je multiplie par le nombre de départements et de sexe sélectionnés
Sûrement perfectible mais cette solution me semble préférable à la colonne calculée et surtout à Power Query

Stéphane

[Pierre Dumas]

Bonsoir

Bravo ! Merci beaucoup. C'est parfait comme solution.
C'est effectivement lourd dans Power Query.

Merci du temps passé.

Bonne nuit

Pierre Dumas

[Raccourcix]

Merci Pierre pour le retour

Les fonctions de "dates intelligentes" sont très utiles en DAX pour ce genre de problème
La mesure suivante est encore plus simple que celle que j'ai proposée rapidement hier soir

Code :

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

1
2
3
4
5
6
7
 
CALCULATE(
	AVERAGE([hosp]);
	DATESINPERIOD(Tableau1[jour];MAX(Tableau1[jour]);-8;DAY)
	)
*DISTINCTCOUNT([dep])
*DISTINCTCOUNT([sexe])

Dans Excel, il ne faut pas se contenter des formules, des TCD, de VBA, de Power Query, de Power Pivot... il faut connaitre et utiliser toutes les possibilités et prendre la meilleure pour répondre à chaque besoin.
C'est ce qui fait la richesse de cet outil et ce forum me permet de pousser mes propres limites !

Stéphane

[Pierre Dumas]

Bonjour Stéphane

C'est encore plus simple comme mesure et bien plus rapide que PowerQuery

Et je suis d'accord avec le fait de ne pas rester sur son terrain de certitudes avec Excel, et qu'il faut toujours agrandir son champs des possibles. Il y a souvent plus d'une solution.

Bonne journée en tout cas et encore

Pierre