topic Re: Canonical Date in Francophones

Canonical Date

stephaniee — Mon, 19 Feb 2018 14:19:33 GMT

Bonjour,

Je dois créer un canonical calendar pour me permettre de comparer des données avec des périmètres de dates différentes.

Dans mon cas, je cherche à avoir l'ensemble des missions reçues en N et l'ensemble des réclamations reçues sur l'année N.

Une mission peut-etre recue en N avec une réclamation en N+1.

Pour cela, je crois comprendre que je dois créer un canonicalcalendar. Et la je commence à être perdue. Pouvez-vous m'aider ?

Les dates concernées par ce calendrier sont :

- Date_Dossier : Correspondant à la date de ma mission

- Date_Heure_Creation : Correspondant à la date de ma réclamation

J'ai chargé mes deux tables :

Set dataManagerTables = '','suivi','gaq_suivi';

//This block renames script tables from non generated section which conflict with the names of managed tables

For each name in $(dataManagerTables)

Let index = 0;

Let currentName = name;

Let tableNumber = TableNumber(name);

Let matches = 0;

Do while not IsNull(tableNumber) or (index > 0 and matches > 0)

index = index + 1;

currentName = name & '-' & index;

tableNumber = TableNumber(currentName)

matches = Match('$(currentName)', $(dataManagerTables));

Loop

If index > 0 then

Rename Table [$(name)] to [$(currentName)];

EndIf;

Next;

LIB CONNECT TO [CapsOffice];

[suivi]:

LOAD

[Num_Dossier],

[Date_Dossier],

Year([Date_Dossier]) AS [Année_Dossier],

[Vehicule_Roulant],

[Id_Type_Incident],

[Id_Assureur] AS [suivi.Id_Assureur],

[Id_Garage] AS [suivi.Id_Garage],

[Mission_Premium],

[Mission_NMD],

[Mission_Hors_Reseau];

SQL SELECT `Num_Dossier`,

`Date_Dossier`,

`Vehicule_Roulant`,

`Id_Type_Incident`,

`Id_Assureur`,

`Id_Garage`,

`Mission_Premium`,

`Mission_NMD`,

`Mission_Hors_Reseau`

FROM `capsoffice`.`suivi`;

[gaq_suivi]:

LOAD

[Id_Reclamation],

[Num_Dossier],

[Id_Responsabilite],

[Id_Canal],

[Statut_Reclamation],

[Id_Garage] AS [gaq_suivi.Id_Garage],

[Id_Assureur] AS [gaq_suivi.Id_Assureur],

[Id_Type_Reclamation],

[Id_Identification],

[Date_Heure_Creation],

[Reclamation_Occurrence],

Year([Date_Heure_Creation]) AS [Année_creation_reclamation];

SQL SELECT `Id_Reclamation`,

`Num_Dossier`,

`Id_Responsabilite`,

`Id_Canal`,

`Statut_Reclamation`,

`Id_Garage`,

`Id_Assureur`,

`Id_Type_Reclamation`,

`Id_Identification`,

`Date_Heure_Creation`,

`Reclamation_Occurrence`

FROM `capsoffice`.`gaq_suivi`;

Un calendrier s'est créé automatiquement :

[autoCalendar]:

DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

Month($1) AS [Month] Tagged ('$month', '$cyclic'),

Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),

If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

Year(Today())-Year($1) AS [YearsAgo] ,

If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,

4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,

Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,

If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,

12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,

Month(Today())-Month($1) AS [MonthRelNo] ,

If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,

(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,

Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Date_Dossier], [Date_Heure_Creation] USING [autoCalendar] ;

Comment procéder à partir de ces éléments pour créer mon canonicalcalendar ?

Encore merci aux personnes qui pourront m'aider ?

Stéphanie

Re: Canonical Date

Anonymous — Mon, 19 Feb 2018 15:41:38 GMT

Bonjour Stéphanie,

Je n'avais jamais entendus le mot "Canonical Calendar"

Tu peux aller voir se post qui explique comment faire tes différents calendriers

Canonical Date

Je peux te mettre ci-dessous le calendrier que j'utilise (tu pourras charger le script tel quel)

// Calendrier sur la date [Date_Dossier] de la table [suivi] => Suivi_Date_Dossier

[MinMaxDates]:

Load

Min(Floor([Date_Dossier])) AS [MinDate],

Max(Floor([Date_Dossier])) AS [MaxDate]

Resident [suivi]

;

Let vMinDate = Peek('MinDate', 0, 'MinMaxDates')*1;

Let vMaxDate = Peek('MaxDate', 0, 'MinMaxDates')*1;

Drop Table [MinMaxDates];

[tmp_Calendar]:

Load

Date($(vMinDate) + RowNo()-1) AS [Date]

AutoGenerate ($(vMaxDate) - $(vMinDate)+1)

;

// Calendrier du Suivis des Dates Dossier => Nom de table : [Calendar_SDD]

[Calendar_SDD]:

Load

[Date] As [Date_SDD],

Date([Date],'DD/MM/YYYY') AS [Date__SDD],

MakeWeekDate(Year([Date])-1, Week([Date]), Num(WeekDay([Date]))) AS [Day_Date_Y-1__SDD], // Day Name Equiv N-1

AddYears([Date],-1) AS [Date_Y-1__SDD], // Date Equiv N-1

Date([Date],'DD') AS [Num_Day_SDD],

Date([Date],'YYYYMMDD') AS [FullDate_SDD],

Week([Date]) AS [Week_SDD],

WeekName([Date]) AS [Week_Name_SDD],

Year(Date)-1 & '/' & Num(Week(Date),'00') AS [Week_Name_Y-1_SDD], // Num Semaine Equiv N-1

Num(Week([Date])) AS [Week_Num_SDD],

Month([Date]) AS [Month_SDD],

MonthName([Date]) AS [Month_Name_SDD],

Num(Month([Date])) AS [Month_Num_SDD],

Year([Date]) AS [Year_SDD],

Year([Date]) & '.' & Week([Date]) AS [Year_Week_SDD],

Week([Date]) & '.' & Year([Date]) AS [Week_Year_SDD],

Num(Dual(Year([Date])&'-'&Week([Date]), weekstart([Date]))) AS [Year_Week_Num_SDD],

Year([Date]) & '.' & Month([Date]) AS [Year_Month_SDD],

Month([Date]) & '.' & Year([Date]) AS [Month_Year_SDD],

Num(Dual(Year([Date])&'-'&Month([Date]), monthstart([Date]))) AS [Year_Month_Num_SDD],

Dual('Q'&Num(Ceil(Num(Month([Date]))/3)),Num(Ceil(Num(Month([Date]))/3),00)) AS [Qarter_SDD],

Dual(Year([Date])&'-Q'&Num(Ceil(Num(Month([Date]))/3)),QuarterStart([Date])) AS [Year_Quarter_SDD]

Resident [tmp_Calendar]

;

Drop Tables [tmp_Calendar];

/*************************************************************************************************************/

// Calendrier sur la date [Date_Heure_Creation] de la table [gaq_suivi] => Suivi_Date_Réclamation

[MinMaxDates]:

Load

Min(Floor([Date_Heure_Creation])) AS [MinDate],

Max(Floor([Date_Heure_Creation])) AS [MaxDate]

Resident [gaq_suivi]

;

Let vMinDate = Peek('MinDate', 0, 'MinMaxDates')*1;

Let vMaxDate = Peek('MaxDate', 0, 'MinMaxDates')*1;

Drop Table [MinMaxDates];

[tmp_Calendar]:

Load

Date($(vMinDate) + RowNo()-1) AS [Date]

AutoGenerate ($(vMaxDate) - $(vMinDate)+1)

;

// Calendrier du Suivis des Dates Réclamation => Nom de table : [Calendar_SDR]

[Calendar_SDR]:

Load

[Date] As [Date_SDR],

Date([Date],'DD/MM/YYYY') AS [Date_SDR],

MakeWeekDate(Year([Date])-1, Week([Date]), Num(WeekDay([Date]))) AS [Day_Date_Y-1_SDR], // Day Name Equiv N-1

AddYears([Date],-1) AS [Date_Y-1_SDR], // Date Equiv N-1

Date([Date],'DD') AS [Num_Day_SDR],

Date([Date],'YYYYMMDD') AS [FullDate_SDR],

Week([Date]) AS [Week_SDR],

WeekName([Date]) AS [Week_Name_SDR],

Year(Date)-1 & '/' & Num(Week(Date),'00') AS [Week_Name_Y-1_SDR], // Num Semaine Equiv N-1

Num(Week([Date])) AS [Week_Num_SDR],

Month([Date]) AS [Month_SDR],

MonthName([Date]) AS [Month_Name_SDR],

Num(Month([Date])) AS [Month_Num_SDR],

Year([Date]) AS [Year_SDR],

Year([Date]) & '.' & Week([Date]) AS [Year_Week_SDR],

Week([Date]) & '.' & Year([Date]) AS [Week_Year_SDR],

Num(Dual(Year([Date])&'-'&Week([Date]), weekstart([Date]))) AS [Year_Week_Num_SDR],

Year([Date]) & '.' & Month([Date]) AS [Year_Month_SDR],

Month([Date]) & '.' & Year([Date]) AS [Month_Year_SDR],

Num(Dual(Year([Date])&'-'&Month([Date]), monthstart([Date]))) AS [Year_Month_Num_SDR],

Dual('Q'&Num(Ceil(Num(Month([Date]))/3)),Num(Ceil(Num(Month([Date]))/3),00)) AS [Qarter_SDR],

Dual(Year([Date])&'-Q'&Num(Ceil(Num(Month([Date]))/3)),QuarterStart([Date])) AS [Year_Quarter_SDR]

Resident [tmp_Calendar]

;

Drop Tables [tmp_Calendar];

/*************************************************************************************************************/

Concernant ta première demande essaye ça

// Nombre de dossier sur l'année Max (nombre de mission)

=Count({$< [Year_SDD] = {"$(=(Max([Year_SDD])))"} >} DISTINCT [Num_Dossier])

// Nombre de Réclamation sur l'année Max

=Count({$< [Year_SDR] = {"$(=(Max([Year_SDR])))"} >} DISTINCT [Num_Dossier])

Re: Canonical Date

sfatoux72 — Mon, 19 Feb 2018 17:27:49 GMT

‌Salut,

Lors du chargement de la table "gaq_suivi", renome le champ "Num_Dossier" en "Tmp_Num_Dossier" (Num_Dossier as Tmp_Num_Dossier )

Puis ajoute le code ci-dessous après le chargement de la table "gaq_suivi" :

DateBridge:

Load

Num_Dossier,

Null() as Id_Reclamation,

Date_Dossier as Canonical_Date

'Date_Dossier' as DateType

Resident suivi;

Concatenate (DateBridge)

Load

Tmp_Num_Dossier as Num_Dossier,

Id_Reclamation,

Date_Heure_Creation as Canonical_Date

'Date_Reclamation' as DateType

Resident gaq_suivi;

Drop Field Tmp_Num_Dossier;

tu pourra alors construire ton calendrier sur le champ "Canonical_Date"

Re: Canonical Date

stephaniee — Tue, 20 Feb 2018 08:45:43 GMT

Salut,

Et merci beaucoup !

Action réalisée et mon chargement fonctionne. Mais je coince sur la création du calendrier.

En découle plusieurs questions :

- Est-ce que je dois créer un calendrier un parrallèle pour chaque date ? (En reprenant les éléments de Boris)

- Ou, est ce que mon calendrier créé automatiquement suffit ?

- Ou, dois je le supprimer ?

Mon calendrier créé à partir des éléments de Boris (que je remercie également).

// Calendrier sur la date [Canonical_Date] de la table [????????]

[MinMaxDates]:

Load

Min(Floor([Canonical_Date])) AS [MinDate],

Max(Floor([Canonical_Date])) AS [MaxDate]

Resident [?????????]

;

Let vMinDate = Peek('MinDate', 0, 'MinMaxDates')*1;

Let vMaxDate = Peek('MaxDate', 0, 'MinMaxDates')*1;

Drop Table [MinMaxDates];

[canonical_Calendar]:

Load

Date($(vMinDate) + RowNo()-1) AS [Date]

AutoGenerate ($(vMaxDate) - $(vMinDate)+1)

;

// Calendrier Globale => Nom de table : [Calendar]

[Calendar]:

Load

[Date] As [Date],

Date([Date],'DD/MM/YYYY') AS [Date],

MakeWeekDate(Year([Date])-1, Week([Date]), Num(WeekDay([Date]))) AS [Day_Date_Y-1], // Day Name Equiv N-1

AddYears([Date],-1) AS [Date_Y-1], // Date Equiv N-1

Date([Date],'DD') AS [Num_Day],

Date([Date],'YYYYMMDD') AS [FullDate],

Week([Date]) AS [Week],

WeekName([Date]) AS [Week_Name],

Year(Date)-1 & '/' & Num(Week(Date),'00') AS [Week_Name_Y-1], // Num Semaine Equiv N-1

Num(Week([Date])) AS [Week_Num],

Month([Date]) AS [Month],

MonthName([Date]) AS [Month_Name],

Num(Month([Date])) AS [Month_Num],

Year([Date]) AS [Year],

Year([Date]) & '.' & Week([Date]) AS [Year_Week],

Week([Date]) & '.' & Year([Date]) AS [Week_Year],

Num(Dual(Year([Date])&'-'&Week([Date]), weekstart([Date]))) AS [Year_Week_Num],

Year([Date]) & '.' & Month([Date]) AS [Year_Month],

Month([Date]) & '.' & Year([Date]) AS [Month_Year],

Num(Dual(Year([Date])&'-'&Month([Date]), monthstart([Date]))) AS [Year_Month_Num],

Dual('Q'&Num(Ceil(Num(Month([Date]))/3)),Num(Ceil(Num(Month([Date]))/3),00)) AS [Qarter],

Dual(Year([Date])&'-Q'&Num(Ceil(Num(Month([Date]))/3)),QuarterStart([Date])) AS [Year_Quarter]

Resident [???????????????]

;

Est-ce que mon calendrier est correct ? Pouvez-vous m'aider sur mes infomations manquantes ?

Encore merci pour votre aide à tous,

Stéphanie

Re: Canonical Date

Anonymous — Tue, 20 Feb 2018 09:37:56 GMT

Bonjour Stéphanie,

Suite au message de Sébastien je comprend mieux ton besoin.

- Est-ce que je dois créer un calendrier en parallèle pour chaque date ? (En reprenant les éléments de Boris)

=> Tout dépend de tes différents besoin dans ton reporting, ne maitrisant pas parfaitement le calendrier canonique je laisserais Sébastien te répondre plus précisément sur ce point.

- Ou, est ce que mon calendrier créé automatiquement suffit ?

=> J'ai vraiment une préférence pour le calendrier que j'ai posté dans mon message précédent par rapport à l'AutoCalendar mais l'AutoCalendar comme le Calendar posté plus haut feront le job correctement l'un comme l'autre.

- Ou, dois je le supprimer ?

=> Comme ci-dessus, à toi de voir le calendrier que tu préfère utilisé, si Sébastien repasse par ici je suis sur qu'il aura une bonne practice à proposer

Concernant ta dernière question :

- Est-ce que mon calendrier est correct ? Pouvez-vous m'aider sur mes informations manquantes ?

// Calendrier sur la date [Canonical_Date] de la table [DateBridge]

// Ton champ [Canonical_Date] se trouve dans la table [DateBridge], nous allons donc nous servir de cette table pour charger le champ [Canonical_Date] dans le calendrier ci-dessous

[MinMaxDates]:

Load

Min(Floor([Canonical_Date])) AS [MinDate],

Max(Floor([Canonical_Date])) AS [MaxDate]

Resident [DateBridge] // ici on utilise la fonction "Resident" pour charger des données d'une table déjà chargé dans Qlik (ici la table est [DateBridge])

;

Let vMinDate = Peek('MinDate', 0, 'MinMaxDates')*1;

Let vMaxDate = Peek('MaxDate', 0, 'MinMaxDates')*1;

Drop Table [MinMaxDates];

// Pour la table ci-dessous et pour l'ensemble de tes calendriers tu peux laisser le nom de table [tmp_Calendar]

[tmp_Calendar]:

Load

Date($(vMinDate) + RowNo()-1) AS [Date]

AutoGenerate ($(vMaxDate) - $(vMinDate)+1)

;

// Calendrier Globale => Nom de table : [Calendar]

// J'utilise régulièrement un préfixe pour les champs de mes différents calendriers (ici CD pour Canonical_Date, tu peux choisir autre chose de plus parlant pour toi)

[Calendar_Canonical]:

Load

[Date] As [Canonical_Date], // renommé le champ de cette façon permettra de faire un rapprochement avec la table [Datebridge]

Date([Date],'DD/MM/YYYY') AS [Date_CD],

MakeWeekDate(Year([Date])-1, Week([Date]), Num(WeekDay([Date]))) AS [Day_Date_Y-1_CD], // Day Name Equiv N-1

AddYears([Date],-1) AS [Date_Y-1_CD], // Date Equiv N-1

Date([Date],'DD') AS [Num_Day_CD],

Date([Date],'YYYYMMDD') AS [FullDate_CD],

Week([Date]) AS [Week_CD],

WeekName([Date]) AS [Week_Name_CD],

Year(Date)-1 & '/' & Num(Week(Date),'00') AS [Week_Name_Y-1_CD], // Num Semaine Equiv N-1

Num(Week([Date])) AS [Week_Num_CD],

Month([Date]) AS [Month_CD],

MonthName([Date]) AS [Month_Name_CD],

Num(Month([Date])) AS [Month_Num_CD],

Year([Date]) AS [Year_CD],

Year([Date]) & '.' & Week([Date]) AS [Year_Week_CD],

Week([Date]) & '.' & Year([Date]) AS [Week_Year_CD],

Num(Dual(Year([Date])&'-'&Week([Date]), weekstart([Date]))) AS [Year_Week_Num_CD],

Year([Date]) & '.' & Month([Date]) AS [Year_Month_CD],

Month([Date]) & '.' & Year([Date]) AS [Month_Year_CD],

Num(Dual(Year([Date])&'-'&Month([Date]), monthstart([Date]))) AS [Year_Month_Num_CD],

Dual('Q'&Num(Ceil(Num(Month([Date]))/3)),Num(Ceil(Num(Month([Date]))/3),00)) AS [Qarter_CD],

Dual(Year([Date])&'-Q'&Num(Ceil(Num(Month([Date]))/3)),QuarterStart([Date])) AS [Year_Quarter_CD]

Resident [tmp_Calendar]

;

Par contre pour être sur d'avoir un rapprochement correcte entre ton calendrier [Calendar_Canonical] et la table [DateBridge] vérifie que les champ [Canonical_Date] du calendrier et de la table [DateBridge] on le même format

Re: Canonical Date

stephaniee — Tue, 20 Feb 2018 10:50:18 GMT

Super! Et merci.

Encore une question (désolée)

: Comment je fais pour vérifier le format de mes dates comme tu le suggères ?

Re: Canonical Date

stephaniee — Tue, 20 Feb 2018 11:22:40 GMT

J'ai testé en reprenant tes calendriers proposés et j'ai un autre souci avec le message d'erreur suivant :

"Field names must be unique within table"

A priori il s'agit du calendrier 2 mais je ne trouve pas l'erreur. La vois-tu ?

// Calendrier sur la date [Date_Dossier] de la table [suivi] => Suivi_Date_Dossier

[MinMaxDates1]:

Load

Min(Floor([Date_Dossier])) AS [MinDate1],

Max(Floor([Date_Dossier])) AS [MaxDate1]

Resident [suivi]

;

Let vMinDate1 = Peek('MinDate1', 0, 'MinMaxDates1')*1;

Let vMaxDate1 = Peek('MaxDate1', 0, 'MinMaxDates1')*1;

Drop Table [MinMaxDates1];

[tmp_Calendar1]:

Load

Date($(vMinDate1) + RowNo()-1) AS [Date1]

AutoGenerate ($(vMaxDate1) - $(vMinDate1)+1)

;

// Calendrier du Suivis des Dates Dossier => Nom de table : [Calendar_SDD]

[Calendar_SDD]:

Load

[Date1] As [Date_SDD],

Date([Date1],'DD/MM/YYYY') AS [Date__SDD],

MakeWeekDate(Year([Date1])-1, Week([Date1]), Num(WeekDay([Date1]))) AS [Day_Date_Y-1__SDD], // Day Name Equiv N-1

AddYears([Date1],-1) AS [Date_Y-1__SDD], // Date Equiv N-1

Date([Date1],'DD') AS [Num_Day_SDD],

Date([Date1],'YYYYMMDD') AS [FullDate_SDD],

Week([Date1]) AS [Week_SDD],

WeekName([Date1]) AS [Week_Name_SDD],

Year([Date1])-1 & '/' & Num(Week([Date1]),'00') AS [Week_Name_Y-1_SDD], // Num Semaine Equiv N-1

Num(Week([Date1])) AS [Week_Num_SDD],

Month([Date1]) AS [Month_SDD],

MonthName([Date1]) AS [Month_Name_SDD],

Num(Month([Date1])) AS [Month_Num_SDD],

Year([Date1]) AS [Year_SDD],

Year([Date1]) & '.' & Week([Date1]) AS [Year_Week_SDD],

Week([Date1]) & '.' & Year([Date1]) AS [Week_Year_SDD],

Num(Dual(Year([Date1])&'-'&Week([Date1]), weekstart([Date1]))) AS [Year_Week_Num_SDD],

Year([Date1]) & '.' & Month([Date1]) AS [Year_Month_SDD],

Month([Date1]) & '.' & Year([Date1]) AS [Month_Year_SDD],

Num(Dual(Year([Date1])&'-'&Month([Date1]), monthstart([Date1]))) AS [Year_Month_Num_SDD],

Dual('Q'&Num(Ceil(Num(Month([Date1]))/3)),Num(Ceil(Num(Month([Date1]))/3),00)) AS [Qarter_SDD],

Dual(Year([Date1])&'-Q'&Num(Ceil(Num(Month([Date1]))/3)),QuarterStart([Date1])) AS [Year_Quarter_SDD]

Resident [tmp_Calendar1]

;

Drop Tables [tmp_Calendar1];

/*************************************************************************************************************/

// Calendrier sur la date [Date_Heure_Creation] de la table [gaq_suivi] => Suivi_Date_Réclamation

[MinMaxDates2]:

Load

Min(Floor([Date_Heure_Creation])) AS [MinDate2],

Max(Floor([Date_Heure_Creation])) AS [MaxDate2]

Resident [gaq_suivi]

;

Let vMinDate2 = Peek('MinDate2', 0, 'MinMaxDates2')*1;

Let vMaxDate2 = Peek('MaxDate2', 0, 'MinMaxDates2')*1;

Drop Table [MinMaxDates2];

[tmp_Calendar2]:

Load

Date($(vMinDate2) + RowNo()-1) AS [Date2]

AutoGenerate ($(vMaxDate2) - $(vMinDate2)+1)

;

// Calendrier du Suivis des Dates Réclamation => Nom de table : [Calendar_SDR]

[Calendar_SDR]:

Load

[Date2] As [Date_SDR],

Date([Date2],'DD/MM/YYYY') AS [Date_SDR],

MakeWeekDate(Year([Date2])-1, Week([Date2]), Num(WeekDay([Date2]))) AS [Day_Date_Y-1_SDR], // Day Name Equiv N-1

AddYears([Date2],-1) AS [Date_Y-1_SDR], // Date Equiv N-1

Date([Date2],'DD') AS [Num_Day_SDR],

Date([Date2],'YYYYMMDD') AS [FullDate_SDR],

Week([Date2]) AS [Week_SDR],

WeekName([Date2]) AS [Week_Name_SDR],

Year([Date2])-1 & '/' & Num(Week([Date2]),'00') AS [Week_Name_Y-1_SDR], // Num Semaine Equiv N-1

Num(Week([Date2])) AS [Week_Num_SDR],

Month([Date2]) AS [Month_SDR],

MonthName([Date2]) AS [Month_Name_SDR],

Num(Month([Date2])) AS [Month_Num_SDR],

Year([Date2]) AS [Year_SDR],

Year([Date2]) & '.' & Week([Date2]) AS [Year_Week_SDR],

Week([Date2]) & '.' & Year([Date2]) AS [Week_Year_SDR],

Num(Dual(Year([Date2])&'-'&Week([Date2]), weekstart([Date2]))) AS [Year_Week_Num_SDR],

Year([Date2]) & '.' & Month([Date2]) AS [Year_Month_SDR],

Month([Date2]) & '.' & Year([Date2]) AS [Month_Year_SDR],

Num(Dual(Year([Date2])&'-'&Month([Date2]), monthstart([Date2]))) AS [Year_Month_Num_SDR],

Dual('Q'&Num(Ceil(Num(Month([Date2]))/3)),Num(Ceil(Num(Month([Date2]))/3),00)) AS [Qarter_SDR],

Dual(Year([Date2])&'-Q'&Num(Ceil(Num(Month([Date2]))/3)),QuarterStart([Date2])) AS [Year_Quarter_SDR]

Resident [tmp_Calendar2]

;

Drop Tables [tmp_Calendar2];

/*************************************************************************************************************/

Re: Canonical Date

Anonymous — Tue, 20 Feb 2018 13:09:45 GMT

Dans une même table deux champs ne peuvent avoir le même nom, dans le deuxième calendrier les deux premiers champs ont été renommé avec un nom identique, du coup cela génère une erreur lors du chargement.

Essaye ce script, j'ai gardé volontairement toutes tes tables, par contre il y aura peut être un problème pour la table [suivi] dans le modèle de donnée.

Cette réponse intègre aussi la réponse de Sébastien du coup Sébastien devra peut être modifier un peu le script ci-dessous pour que l'ensemble soit cohérent.

[suivi]:

LOAD

[Num_Dossier] As [Tmp_Num_Dossier],

[Date_Dossier],

Date(Floor([Date_Dossier]),'DD/MM/YYYY') AS [Date_Calendar_Date_Dossier],

[Vehicule_Roulant],

[Id_Type_Incident],

[Id_Assureur] AS [suivi.Id_Assureur],

[Id_Garage] AS [suivi.Id_Garage],

[Mission_Premium],

[Mission_NMD],

[Mission_Hors_Reseau];

SQL SELECT

`Num_Dossier`,

`Date_Dossier`,

`Vehicule_Roulant`,

`Id_Type_Incident`,

`Id_Assureur`,

`Id_Garage`,

`Mission_Premium`,

`Mission_NMD`,

`Mission_Hors_Reseau`

FROM `capsoffice`.`suivi`;

[gaq_suivi]:

LOAD

[Id_Reclamation],

[Num_Dossier],

[Id_Responsabilite],

[Id_Canal],

[Statut_Reclamation],

[Id_Garage] AS [gaq_suivi.Id_Garage],

[Id_Assureur] AS [gaq_suivi.Id_Assureur],

[Id_Type_Reclamation],

[Id_Identification],

[Date_Heure_Creation],

Date(Floor([Date_Heure_Creation]),'DD/MM/YYYY') As [Date_Calendar_Date_Heure_Creation],

[Reclamation_Occurrence],

Year([Date_Heure_Creation]) AS [Année_creation_reclamation];

SQL SELECT

`Id_Reclamation`,

`Num_Dossier`,

`Id_Responsabilite`,

`Id_Canal`,

`Statut_Reclamation`,

`Id_Garage`,

`Id_Assureur`,

`Id_Type_Reclamation`,

`Id_Identification`,

`Date_Heure_Creation`,

`Reclamation_Occurrence`

FROM `capsoffice`.`gaq_suivi`;

[DateBridge]:

Load

Num_Dossier,

Null() as Id_Reclamation,

Date(Floor(Date_Dossier),'DD/MM/YYYY') as Canonical_Date, // Tu t'assure que la date est sur le format DD/MM/YYYY

'Date_Dossier' as DateType

Resident suivi;

Concatenate (DateBridge)

Load

Tmp_Num_Dossier as Num_Dossier,

Id_Reclamation,

Date(Floor(Date_Heure_Creation),'DD/MM/YYYY') as Canonical_Date, // Tu t'assure que la date est sur le format DD/MM/YYYY

'Date_Reclamation' as DateType

Resident gaq_suivi;

Drop Field Tmp_Num_Dossier;

/*************************************************************************************************************/

// Calendrier sur la date [Date_Dossier] de la table [suivi] => Suivi_Date_Dossier

[MinMaxDates1]:

Load

Min(Floor([Date_Dossier])) AS [MinDate1],

Max(Floor([Date_Dossier])) AS [MaxDate1]

Resident [suivi]

;

Let vMinDate1 = Peek('MinDate1', 0, 'MinMaxDates1')*1;

Let vMaxDate1 = Peek('MaxDate1', 0, 'MinMaxDates1')*1;

Drop Table [MinMaxDates1];

[tmp_Calendar1]:

Load

Date($(vMinDate1) + RowNo()-1) AS [Date1]

AutoGenerate ($(vMaxDate1) - $(vMinDate1)+1)

;

// Calendrier du Suivis des Dates Dossier => Nom de table : [Calendar_SDD]

[Calendar_SDD]:

Load

[Date1] As [Date_Calendar_Date_Dossier], // Renommé le champ permettra le rapprochement entre le calendrier et la table [suivi]

Date([Date1],'DD/MM/YYYY') AS [Date_SDD],

MakeWeekDate(Year([Date1])-1, Week([Date1]), Num(WeekDay([Date1]))) AS [Day_Date_Y-1_SDD], // Day Name Equiv N-1

AddYears([Date1],-1) AS [Date_Y-1_SDD], // Date Equiv N-1

Date([Date1],'DD') AS [Num_Day_SDD],

Date([Date1],'YYYYMMDD') AS [FullDate_SDD],

Week([Date1]) AS [Week_SDD],

WeekName([Date1]) AS [Week_Name_SDD],

Year([Date1])-1 & '/' & Num(Week([Date1]),'00') AS [Week_Name_Y-1_SDD], // Num Semaine Equiv N-1

Num(Week([Date1])) AS [Week_Num_SDD],

Month([Date1]) AS [Month_SDD],

MonthName([Date1]) AS [Month_Name_SDD],

Num(Month([Date1])) AS [Month_Num_SDD],

Year([Date1]) AS [Year_SDD],

Year([Date1]) & '.' & Week([Date1]) AS [Year_Week_SDD],

Week([Date1]) & '.' & Year([Date1]) AS [Week_Year_SDD],

Num(Dual(Year([Date1])&'-'&Week([Date1]), weekstart([Date1]))) AS [Year_Week_Num_SDD],

Year([Date1]) & '.' & Month([Date1]) AS [Year_Month_SDD],

Month([Date1]) & '.' & Year([Date1]) AS [Month_Year_SDD],

Num(Dual(Year([Date1])&'-'&Month([Date1]), monthstart([Date1]))) AS [Year_Month_Num_SDD],

Dual('Q'&Num(Ceil(Num(Month([Date1]))/3)),Num(Ceil(Num(Month([Date1]))/3),00)) AS [Qarter_SDD],

Dual(Year([Date1])&'-Q'&Num(Ceil(Num(Month([Date1]))/3)),QuarterStart([Date1])) AS [Year_Quarter_SDD]

Resident [tmp_Calendar1]

;

Drop Tables [tmp_Calendar1];

/*************************************************************************************************************/

// Calendrier sur les dates [Date_Heure_Creation] de la table [gaq_suivi] => Suivi_Date_Réclamation

[MinMaxDates2]:

Load

Min(Floor([Date_Heure_Creation])) AS [MinDate2],

Max(Floor([Date_Heure_Creation])) AS [MaxDate2]

Resident [gaq_suivi]

;

Let vMinDate2 = Peek('MinDate2', 0, 'MinMaxDates2')*1;

Let vMaxDate2 = Peek('MaxDate2', 0, 'MinMaxDates2')*1;

Drop Table [MinMaxDates2];

[tmp_Calendar2]:

Load

Date($(vMinDate2) + RowNo()-1) AS [Date2]

AutoGenerate ($(vMaxDate2) - $(vMinDate2)+1)

;

// Calendrier sur les dates Dates Réclamation => Nom de table : [Calendar_SDR]

[Calendar_SDR]:

Load

[Date2] As [Date_Calendar_Date_Heure_Creation], // Renommé le champ permettra le rapprochement entre le calendrier et la table [gaq_suivi]

Date([Date2],'DD/MM/YYYY') AS [Date_SDR],

MakeWeekDate(Year([Date2])-1, Week([Date2]), Num(WeekDay([Date2]))) AS [Day_Date_Y-1_SDR], // Day Name Equiv N-1

AddYears([Date2],-1) AS [Date_Y-1_SDR], // Date Equiv N-1

Date([Date2],'DD') AS [Num_Day_SDR],

Date([Date2],'YYYYMMDD') AS [FullDate_SDR],

Week([Date2]) AS [Week_SDR],

WeekName([Date2]) AS [Week_Name_SDR],

Year([Date2])-1 & '/' & Num(Week([Date2]),'00') AS [Week_Name_Y-1_SDR], // Num Semaine Equiv N-1

Num(Week([Date2])) AS [Week_Num_SDR],

Month([Date2]) AS [Month_SDR],

MonthName([Date2]) AS [Month_Name_SDR],

Num(Month([Date2])) AS [Month_Num_SDR],

Year([Date2]) AS [Year_SDR],

Year([Date2]) & '.' & Week([Date2]) AS [Year_Week_SDR],

Week([Date2]) & '.' & Year([Date2]) AS [Week_Year_SDR],

Num(Dual(Year([Date2])&'-'&Week([Date2]), weekstart([Date2]))) AS [Year_Week_Num_SDR],

Year([Date2]) & '.' & Month([Date2]) AS [Year_Month_SDR],

Month([Date2]) & '.' & Year([Date2]) AS [Month_Year_SDR],

Num(Dual(Year([Date2])&'-'&Month([Date2]), monthstart([Date2]))) AS [Year_Month_Num_SDR],

Dual('Q'&Num(Ceil(Num(Month([Date2]))/3)),Num(Ceil(Num(Month([Date2]))/3),00)) AS [Qarter_SDR],

Dual(Year([Date2])&'-Q'&Num(Ceil(Num(Month([Date2]))/3)),QuarterStart([Date2])) AS [Year_Quarter_SDR]

Resident [tmp_Calendar2]

;

Drop Tables [tmp_Calendar2];

/*************************************************************************************************************/

// Calendrier sur la date [Canonical_Date] de la table [DateBridge]

[MinMaxDates2]:

Load

Min(Floor([Canonical_Date])) AS [MinDate3],

Max(Floor([Canonical_Date])) AS [MaxDate3]

Resident [DateBridge]

;

Let vMinDate3 = Peek('MinDate3', 0, 'MinMaxDates3')*1;

Let vMaxDate3 = Peek('MaxDate3', 0, 'MinMaxDates3')*1;

Drop Table [MinMaxDates3];

[tmp_Calendar3]:

Load

Date($(vMinDate3) + RowNo()-1) AS [Date3]

AutoGenerate ($(vMaxDate3) - $(vMinDate3)+1)

;

// Calendrier sur les date [Canonical_Date] => Nom de table : [Calendar_DB]

[Calendar_DB]:

Load

[Date3] As [Canonical_Date], // Renommé le champ permettra le rapprochement entre le calendrier et la table [gaq_suivi]

Date([Date3],'DD/MM/YYYY') AS [Date_DB],

MakeWeekDate(Year([Date3])-1, Week([Date2]), Num(WeekDay([Date2]))) AS [Day_Date_Y-1_DB], // Day Name Equiv N-1

AddYears([Date3],-1) AS [Date_Y-1_DB], // Date Equiv N-1

Date([Date3],'DD') AS [Num_Day_DB],

Date([Date3],'YYYYMMDD') AS [FullDate_DB],

Week([Date3]) AS [Week_DB],

WeekName([Date3]) AS [Week_Name_DB],

Year([Date3])-1 & '/' & Num(Week([Date3]),'00') AS [Week_Name_Y-1_DB], // Num Semaine Equiv N-1

Num(Week([Date3])) AS [Week_Num_DB],

Month([Date3]) AS [Month_DB],

MonthName([Date3]) AS [Month_Name_DB],

Num(Month([Date3])) AS [Month_Num_DB],

Year([Date3]) AS [Year_DB],

Year([Date3]) & '.' & Week([Date3]) AS [Year_Week_DB],

Week([Date3]) & '.' & Year([Date3]) AS [Week_Year_DB],

Num(Dual(Year([Date3])&'-'&Week([Date3]), weekstart([Date3]))) AS [Year_Week_Num_DB],

Year([Date3]) & '.' & Month([Date3]) AS [Year_Month_DB],

Month([Date3]) & '.' & Year([Date3]) AS [Month_Year_DB],

Num(Dual(Year([Date3])&'-'&Month([Date3]), monthstart([Date3]))) AS [Year_Month_Num_DB],

Dual('Q'&Num(Ceil(Num(Month([Date3]))/3)),Num(Ceil(Num(Month([Date3]))/3),00)) AS [Qarter_DB],

Dual(Year([Date3])&'-Q'&Num(Ceil(Num(Month([Date3]))/3)),QuarterStart([Date3])) AS [Year_Quarter_DB]

Resident [tmp_Calendar3]

;

Drop Tables [tmp_Calendar3];

Re: Canonical Date

sfatoux72 — Tue, 20 Feb 2018 14:30:20 GMT

C'est dans la table gap_suivi qu'il faut renommer le champ Num_Dossier en Tmp_Num_Dossier

Normalement tu n'as pas besoin des 3 calendriers, le dernier sur Canonical_Date devrait suffire. Mais je te conseille de garder le 2 premier temporairement pour que tu puisse essayer de réaliser tes expressions avec les 2 premiers calendriers et avec le dernier pour t'exercer. La plupart des expressions pourront être réalisées des 2 façon , mais certaines nécessiteront l'utilisation de Canonical_Date

[suivi]:

LOAD

[Num_Dossier],

[Date_Dossier],

Date(Floor([Date_Dossier]),'DD/MM/YYYY') AS [Date_Calendar_Date_Dossier],

[Vehicule_Roulant],

[Id_Type_Incident],

[Id_Assureur] AS [suivi.Id_Assureur],

[Id_Garage] AS [suivi.Id_Garage],

[Mission_Premium],

[Mission_NMD],

[Mission_Hors_Reseau];

SQL SELECT

`Num_Dossier`,

`Date_Dossier`,

`Vehicule_Roulant`,

`Id_Type_Incident`,

`Id_Assureur`,

`Id_Garage`,

`Mission_Premium`,

`Mission_NMD`,

`Mission_Hors_Reseau`

FROM `capsoffice`.`suivi`;

[gaq_suivi]:

LOAD

[Id_Reclamation],

[Num_Dossier] As [Tmp_Num_Dossier],

[Id_Responsabilite],

[Id_Canal],

[Statut_Reclamation],

[Id_Garage] AS [gaq_suivi.Id_Garage],

[Id_Assureur] AS [gaq_suivi.Id_Assureur],

[Id_Type_Reclamation],

[Id_Identification],

[Date_Heure_Creation],

Date(Floor([Date_Heure_Creation]),'DD/MM/YYYY') As [Date_Calendar_Date_Heure_Creation],

[Reclamation_Occurrence],

Year([Date_Heure_Creation]) AS [Année_creation_reclamation];

SQL SELECT

`Id_Reclamation`,

`Num_Dossier`,

`Id_Responsabilite`,

`Id_Canal`,

`Statut_Reclamation`,

`Id_Garage`,

`Id_Assureur`,

`Id_Type_Reclamation`,

`Id_Identification`,

`Date_Heure_Creation`,

`Reclamation_Occurrence`

FROM `capsoffice`.`gaq_suivi`;

[DateBridge]:

Load

Num_Dossier,

Null() as Id_Reclamation,

Date(Floor(Date_Dossier),'DD/MM/YYYY') as Canonical_Date, // Tu t'assure que la date est sur le format DD/MM/YYYY

'Date_Dossier' as DateType

Resident suivi;

Concatenate (DateBridge)

Load

Tmp_Num_Dossier as Num_Dossier,

Id_Reclamation,

Date(Floor(Date_Heure_Creation),'DD/MM/YYYY') as Canonical_Date, // Tu t'assure que la date est sur le format DD/MM/YYYY

'Date_Reclamation' as DateType

Resident gaq_suivi;

Drop Field Tmp_Num_Dossier;

/*************************************************************************************************************/

// Calendrier sur la date [Date_Dossier] de la table [suivi] => Suivi_Date_Dossier

[MinMaxDates1]:

Load

Min(Floor([Date_Dossier])) AS [MinDate1],

Max(Floor([Date_Dossier])) AS [MaxDate1]

Resident [suivi]

;

Let vMinDate1 = Peek('MinDate1', 0, 'MinMaxDates1')*1;

Let vMaxDate1 = Peek('MaxDate1', 0, 'MinMaxDates1')*1;

Drop Table [MinMaxDates1];

[tmp_Calendar1]:

Load

Date($(vMinDate1) + RowNo()-1) AS [Date1]

AutoGenerate ($(vMaxDate1) - $(vMinDate1)+1)

;

// Calendrier du Suivis des Dates Dossier => Nom de table : [Calendar_SDD]

[Calendar_SDD]:

Load

[Date1] As [Date_Calendar_Date_Dossier], // Renommé le champ permettra le rapprochement entre le calendrier et la table [suivi]

Date([Date1],'DD/MM/YYYY') AS [Date_SDD],

MakeWeekDate(Year([Date1])-1, Week([Date1]), Num(WeekDay([Date1]))) AS [Day_Date_Y-1_SDD], // Day Name Equiv N-1

AddYears([Date1],-1) AS [Date_Y-1_SDD], // Date Equiv N-1

Date([Date1],'DD') AS [Num_Day_SDD],

Date([Date1],'YYYYMMDD') AS [FullDate_SDD],

Week([Date1]) AS [Week_SDD],

WeekName([Date1]) AS [Week_Name_SDD],

Year([Date1])-1 & '/' & Num(Week([Date1]),'00') AS [Week_Name_Y-1_SDD], // Num Semaine Equiv N-1

Num(Week([Date1])) AS [Week_Num_SDD],

Month([Date1]) AS [Month_SDD],

MonthName([Date1]) AS [Month_Name_SDD],

Num(Month([Date1])) AS [Month_Num_SDD],

Year([Date1]) AS [Year_SDD],

Year([Date1]) & '.' & Week([Date1]) AS [Year_Week_SDD],

Week([Date1]) & '.' & Year([Date1]) AS [Week_Year_SDD],

Num(Dual(Year([Date1])&'-'&Week([Date1]), weekstart([Date1]))) AS [Year_Week_Num_SDD],

Year([Date1]) & '.' & Month([Date1]) AS [Year_Month_SDD],

Month([Date1]) & '.' & Year([Date1]) AS [Month_Year_SDD],

Num(Dual(Year([Date1])&'-'&Month([Date1]), monthstart([Date1]))) AS [Year_Month_Num_SDD],

Dual('Q'&Num(Ceil(Num(Month([Date1]))/3)),Num(Ceil(Num(Month([Date1]))/3),00)) AS [Qarter_SDD],

Dual(Year([Date1])&'-Q'&Num(Ceil(Num(Month([Date1]))/3)),QuarterStart([Date1])) AS [Year_Quarter_SDD]

Resident [tmp_Calendar1]

;

Drop Tables [tmp_Calendar1];

/*************************************************************************************************************/

// Calendrier sur les dates [Date_Heure_Creation] de la table [gaq_suivi] => Suivi_Date_Réclamation

[MinMaxDates2]:

Load

Min(Floor([Date_Heure_Creation])) AS [MinDate2],

Max(Floor([Date_Heure_Creation])) AS [MaxDate2]

Resident [gaq_suivi]

;

Let vMinDate2 = Peek('MinDate2', 0, 'MinMaxDates2')*1;

Let vMaxDate2 = Peek('MaxDate2', 0, 'MinMaxDates2')*1;

Drop Table [MinMaxDates2];

[tmp_Calendar2]:

Load

Date($(vMinDate2) + RowNo()-1) AS [Date2]

AutoGenerate ($(vMaxDate2) - $(vMinDate2)+1)

;

// Calendrier sur les dates Dates Réclamation => Nom de table : [Calendar_SDR]

[Calendar_SDR]:

Load

[Date2] As [Date_Calendar_Date_Heure_Creation], // Renommé le champ permettra le rapprochement entre le calendrier et la table [gaq_suivi]

Date([Date2],'DD/MM/YYYY') AS [Date_SDR],

MakeWeekDate(Year([Date2])-1, Week([Date2]), Num(WeekDay([Date2]))) AS [Day_Date_Y-1_SDR], // Day Name Equiv N-1

AddYears([Date2],-1) AS [Date_Y-1_SDR], // Date Equiv N-1

Date([Date2],'DD') AS [Num_Day_SDR],

Date([Date2],'YYYYMMDD') AS [FullDate_SDR],

Week([Date2]) AS [Week_SDR],

WeekName([Date2]) AS [Week_Name_SDR],

Year([Date2])-1 & '/' & Num(Week([Date2]),'00') AS [Week_Name_Y-1_SDR], // Num Semaine Equiv N-1

Num(Week([Date2])) AS [Week_Num_SDR],

Month([Date2]) AS [Month_SDR],

MonthName([Date2]) AS [Month_Name_SDR],

Num(Month([Date2])) AS [Month_Num_SDR],

Year([Date2]) AS [Year_SDR],

Year([Date2]) & '.' & Week([Date2]) AS [Year_Week_SDR],

Week([Date2]) & '.' & Year([Date2]) AS [Week_Year_SDR],

Num(Dual(Year([Date2])&'-'&Week([Date2]), weekstart([Date2]))) AS [Year_Week_Num_SDR],

Year([Date2]) & '.' & Month([Date2]) AS [Year_Month_SDR],

Month([Date2]) & '.' & Year([Date2]) AS [Month_Year_SDR],

Num(Dual(Year([Date2])&'-'&Month([Date2]), monthstart([Date2]))) AS [Year_Month_Num_SDR],

Dual('Q'&Num(Ceil(Num(Month([Date2]))/3)),Num(Ceil(Num(Month([Date2]))/3),00)) AS [Qarter_SDR],

Dual(Year([Date2])&'-Q'&Num(Ceil(Num(Month([Date2]))/3)),QuarterStart([Date2])) AS [Year_Quarter_SDR]

Resident [tmp_Calendar2]

;

Drop Tables [tmp_Calendar2];

/*************************************************************************************************************/

// Calendrier sur la date [Canonical_Date] de la table [DateBridge]

[MinMaxDates2]:

Load

Min(Floor([Canonical_Date])) AS [MinDate3],

Max(Floor([Canonical_Date])) AS [MaxDate3]

Resident [DateBridge]

;

Let vMinDate3 = Peek('MinDate3', 0, 'MinMaxDates3')*1;

Let vMaxDate3 = Peek('MaxDate3', 0, 'MinMaxDates3')*1;

Drop Table [MinMaxDates3];

[tmp_Calendar3]:

Load

Date($(vMinDate3) + RowNo()-1) AS [Date3]

AutoGenerate ($(vMaxDate3) - $(vMinDate3)+1)

;

// Calendrier sur les date [Canonical_Date] => Nom de table : [Calendar_DB]

[Calendar_DB]:

Load

[Date3] As [Canonical_Date], // Renommé le champ permettra le rapprochement entre le calendrier et la table [gaq_suivi]

Date([Date3],'DD/MM/YYYY') AS [Date_DB],

MakeWeekDate(Year([Date3])-1, Week([Date2]), Num(WeekDay([Date2]))) AS [Day_Date_Y-1_DB], // Day Name Equiv N-1

AddYears([Date3],-1) AS [Date_Y-1_DB], // Date Equiv N-1

Date([Date3],'DD') AS [Num_Day_DB],

Date([Date3],'YYYYMMDD') AS [FullDate_DB],

Week([Date3]) AS [Week_DB],

WeekName([Date3]) AS [Week_Name_DB],

Year([Date3])-1 & '/' & Num(Week([Date3]),'00') AS [Week_Name_Y-1_DB], // Num Semaine Equiv N-1

Num(Week([Date3])) AS [Week_Num_DB],

Month([Date3]) AS [Month_DB],

MonthName([Date3]) AS [Month_Name_DB],

Num(Month([Date3])) AS [Month_Num_DB],

Year([Date3]) AS [Year_DB],

Year([Date3]) & '.' & Week([Date3]) AS [Year_Week_DB],

Week([Date3]) & '.' & Year([Date3]) AS [Week_Year_DB],

Num(Dual(Year([Date3])&'-'&Week([Date3]), weekstart([Date3]))) AS [Year_Week_Num_DB],

Year([Date3]) & '.' & Month([Date3]) AS [Year_Month_DB],

Month([Date3]) & '.' & Year([Date3]) AS [Month_Year_DB],

Num(Dual(Year([Date3])&'-'&Month([Date3]), monthstart([Date3]))) AS [Year_Month_Num_DB],

Dual('Q'&Num(Ceil(Num(Month([Date3]))/3)),Num(Ceil(Num(Month([Date3]))/3),00)) AS [Qarter_DB],

Dual(Year([Date3])&'-Q'&Num(Ceil(Num(Month([Date3]))/3)),QuarterStart([Date3])) AS [Year_Quarter_DB]

Resident [tmp_Calendar3]

;

Drop Tables [tmp_Calendar3];

Re: Canonical Date

Anonymous — Tue, 20 Feb 2018 14:54:49 GMT

Bonjour Sébastien,

Autant pour moi en effet, j'ai lu ta réponse un peu rapidement, et donc je me suis trompé de table (suivi à la place de gaq_suivi) et en plus j'ai recopié ta solution bêtement sans me rendre compte de mon erreur

@Stéphanie tu dois prendre directement le script dans la réponse de Sébastien, le mien comporte des erreurs

@Sébastien pourrais tu me donner un exemple d'expression nécessitant uniquement le calendrier Canonique, je suis curieux de savoir quel type de demande pourrait nécessiter un tel calendrier

Re: Canonical Date

sfatoux72 — Wed, 21 Feb 2018 07:29:22 GMT

‌Salut Boris,

J‘ai dit expression, mais pour être plus précis j’aurai dû dire situation.

Par exemple, on ne pourra pas tracer le nombre de dossIer ouvert et le nombre de réclamation dans le même graphique en utilisant un axe temporel commun si on a pas le calendrier canonique à disposition.

Re: Canonical Date

Anonymous — Wed, 21 Feb 2018 08:16:01 GMT

Bonjour Sébastien,

Merci beaucoup pour l'exemple, dans une expression je pense qu'on peut régler le problème avec un "possible" ou un "exclude", mais en effet dans un graphique obligé de passer par ce type de calendrier.

Merci à toi pour l'explication

Re: Canonical Date

stephaniee — Wed, 21 Feb 2018 12:40:13 GMT

Salut,

Et un grand merci ! Cela fonctionne (presque!)

Allez encore une question :

Je veux compter le nombre de mission sur l'année N. J'ai testé deux formules :

=Count({<Mission_Hors_Reseau={'0'}>} distinct([Num_Dossier]))

-> elle fonctionne mais j'ai trop de dossiers.

=Count({Mission_Hors_Reseau={'0'},$< [Year_SDD] = {"$(=(Max([Year_SDD])))"} >} DISTINCT [Num_Dossier])

-> KO - Aucune donnée ne ressort.

A noter qu'en filtre j'ai mis sur l'année :

- Création d'une dimension "Année" : Year_DB

- Création d'une dimension "Mois" : Month_DB

Où est mon erreur/incompréhension ?

Merci,

Stéphanie

Re: Canonical Date

sfatoux72 — Wed, 21 Feb 2018 13:29:05 GMT

‌

Le set analysis c’est : {$<champ1 = {valeur1}, champ2 = {valeur2}, ...>}

EM’essaye comme ça :

Count({$<Mission_Hors_Reseau={'0'}, [Year_SDD] = {"$(=(Max([Year_SDD])))"} >} DISTINCT [Num_Dossier])

Re: Canonical Date

stephaniee — Wed, 21 Feb 2018 14:02:17 GMT

Ok- Elle fonctionne cette formule avec des données conformes sur les volumes à l'année, mais uniquement pour l'année au niveau du filtre.

Avec ma dimension Month_DB, les résultats au mois ne sont pas conformes. As-tu une idée de mon nouveau problème ?

En gros, j'ai le bon nombre de missions en Janvier mais les mois suivants : soit j'en ai trop, soit pas assez...

😞

Re: Canonical Date

sfatoux72 — Wed, 21 Feb 2018 18:03:54 GMT

‌il faut ajouter dans le set analysis un filtre sur le champ DateType, sinon tu as des dossiers qui ont des réclamations dans le mois que tu as selectionné. Attention, il ne faut pas oublier que le calendrier canonique et lié au 2 dates

essaye comme ça:

Count({$<Mission_Hors_Reseau={'0'}, [Year_SDD] = {"$(=(Max([Year_SDD])))"} , DateType = {'Date_Dossier'}>} DISTINCT [Num_Dossier])

Re: Canonical Date

stephaniee — Thu, 22 Feb 2018 13:39:46 GMT

Super ! Merci beaucoup! Cela fonctionne parfaitement.

J'ai voulu reproduire pour calculer mon volume de réclamation, et je dois encore avoir une coquille dans ma formule.

Peux-tu y jeter un oeil ? Si je dois faire pareil pour N-1

Count({$<[Id_Reclamation],

[Id_Type_Reclamation]={'QG'},

[Reclamation_Occurrence]={'1'},

[Id_Responsabilite]={'1','22'},

[Id_Canal]={'3','8','9'},[Year_SDR] = {"$(=(Max([Year_SDR])))"} , DateType = {'Date_Heure_Creation'}>}

Distinct [Num_Dossier])

Si je dois faire pareil pour N-1 avec cette formule, je m'y prends comment :

Count({$<Mission_Hors_Reseau={'0'}, [Year_SDD] = {"$(=(Max([Year_SDD])))"} , DateType = {'Date_Dossier'}>} DISTINCT [Num_Dossier])

Re: Canonical Date

Anonymous — Thu, 22 Feb 2018 15:17:21 GMT

Bonjour Stéphanie,

J'utilise régulièrement l'objet "Indicateur KPi" pour vérifier mes Set Analylis

Dans la formule ci-dessous ce qui nous intéresse c'est l'aspect du N / N-1

Count({$<Mission_Hors_Reseau={'0'}, [Year_SDD] = {"$(=(Max([Year_SDD])))"} , DateType = {'Date_Dossier'}>} DISTINCT [Num_Dossier])

Tu peux ajouter la partie en gras dans un objet "Indicateur KPi"

=(Max([Year_SDD])) ==> doit te donner "2018"

=(Max([Year_SDD]))-1 ==> doit te donner "2017"

En ajoutant "-1" comme ci-dessus tu devrait avoir le résultat que tu désire

Essaye la formule ci-dessous

Count({$<Mission_Hors_Reseau={'0'}, [Year_SDD] = {"$(=(Max([Year_SDD]))-1)"} , DateType = {'Date_Dossier'}>} DISTINCT [Num_Dossier])

Re: Canonical Date

stephaniee — Thu, 22 Feb 2018 15:29:02 GMT

Exact ! Cela fonctionne pour 2017 et 2018.

Par contre si je veux comparer 2016 et 2015, cela ne fonctionne pas..

Je suis sur que tu as un autre tuyau à me filer...

Re: Canonical Date

Anonymous — Thu, 22 Feb 2018 15:48:11 GMT

Comme tu utilise le champ " [Year_SDD] " dans ton set si tu crée un filtre avec ce même champs et que tu choisis l'année 2016 dans ton filtre, ton expression avec "Max(Year_SDD)" sera calculer sur l'année 2016 et ton expression avec "Max(Year_SDD)-1" sera calculer sur l'année 2015