26 Replies Latest reply: Feb 23, 2018 11:00 AM by Boris Lepage RSS

    Canonical Date

    Stéphanie EUSEBE

      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
          Boris Lepage

          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
            Sébastien Fatoux

            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
                Stéphanie EUSEBE

                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
                    Boris Lepage

                    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
                        Stéphanie EUSEBE

                        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
                          Stéphanie EUSEBE

                          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
                              Boris Lepage

                              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
                                  Sébastien Fatoux

                                  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
                                      Boris Lepage

                                      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
                                          Sébastien Fatoux

                                          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
                                              Boris Lepage

                                              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
                                            Stéphanie EUSEBE

                                            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
                                                Sébastien Fatoux

                                                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
                                                    Stéphanie EUSEBE

                                                    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
                                                        Sébastien Fatoux

                                                        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
                                                            Stéphanie EUSEBE

                                                            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
                                                                Boris Lepage

                                                                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
                                                                    Stéphanie EUSEBE

                                                                    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
                                                                        Boris Lepage

                                                                        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

                                                                          • Re: Canonical Date
                                                                            Stéphanie EUSEBE

                                                                            En fait j'ai repris ta formule à ISO sur N et N-1 avec pour filtre l'année basée sur la champ Year_DB.

                                                                             

                                                                            Et j'ai le sentiment que le filtre ne fonctionne plus du tout... !

                                                                              • Re: Canonical Date
                                                                                Boris Lepage

                                                                                Si tu utilise le filtre Year_DB dans ta feuille remplace Year_SDD dans la formule par Year_DB



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



                                                                                  • Re: Canonical Date
                                                                                    Stéphanie EUSEBE

                                                                                    Que dire à part un grand merci ! Cela fonctionne... C'est top!

                                                                                     

                                                                                    Est-ce que je peux encore bénéficier de ton aide ? Pourquoi celle-ci de formule ne fonctionne pas ? Elle a pour objet de compter mon nombre de réclamation.. Je pense avoir repris la même structure mais avec plus de critères.

                                                                                     

                                                                                    Count({$<[Id_Reclamation],

                                                                                    [Id_Type_Reclamation]={'QG'},

                                                                                    [Reclamation_Occurrence]={'1'},

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

                                                                                    [Id_Canal]={'3','8','9'},

                                                                                    [Year_DB] = {"$(=(Max([Year_DB])))"} , DateType = {'Date_Heure_Creation'}>}

                                                                                    DISTINCT [Num_Dossier])

                                                                                      • Re: Canonical Date
                                                                                        Boris Lepage

                                                                                        Avec cette formule tu compte toujours un nombre de "Num_Dossier" par contre la date type est bien celle des Id_Reclamation. Essaye en enlevant le premier champ de ton set comme ci-dessous

                                                                                        Count(

                                                                                        {$<

                                                                                        [Id_Reclamation],

                                                                                        [Id_Type_Reclamation]={'QG'},

                                                                                        [Reclamation_Occurrence]={'1'},

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

                                                                                        [Id_Canal]={'3','8','9'},

                                                                                        [Year_DB] = {"$(=(Max([Year_DB])))"} ,

                                                                                        DateType = {'Date_Heure_Creation'}

                                                                                        >}

                                                                                        DISTINCT [Num_Dossier])

                                                                                         

                                                                                        ou essaye la formule ci-dessous

                                                                                         

                                                                                        Count(

                                                                                        {$<

                                                                                        [Id_Type_Reclamation]={'QG'},

                                                                                        [Reclamation_Occurrence]={'1'},

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

                                                                                        [Id_Canal]={'3','8','9'},

                                                                                        [Year_DB] = {"$(=(Max([Year_DB])))"} ,

                                                                                        DateType = {'Date_Heure_Creation'}

                                                                                        >}

                                                                                        DISTINCT [Id_Reclamation])