4 Replies Latest reply: Mar 23, 2018 5:23 AM by Oussema Laaribi RSS

    master calender

    sahaja qlik

      Hi all,

       

      i  have datefileds like this

       

       

      i want to create master calender   in which i want datefiled

      which will come as 1/1/2018

                                    1/2/2018

      and also i need week , month,  year fileds in master calender.

      can anyone give me script please

        • Re: master calender
          Prashant Sangle

          search on community there is multiple thread available for master calendar

           

          try below

          Creating A Master Calendar

           

          modify it as per your requirement.

           

          Regards,

          • Re: master calender
            Jyothish KC

            see the highlighted format in red

             

             

            Load

            DISTINCT

            Date(Date#('1/1/2017') + recno() -1,'D/M/YYYY') as [Date],

            Year(Date(Date#('1/1/2017') + recno() -1)) as Year,

            year(yearstart(Date(Date#('1/1/2017') + recno() -1),1,7)) as [Fiscal Year],

            yearstart(Date(Date#('1/1/2017') + recno() -1),0,7) as [Fiscal Year Start],

            if(num(Month(Date(Date#('1/1/2017') + recno() -1)))<7, num(Month(Date(Date#('1/1/2017') + recno() -1)))+6,num(Month(Date(Date#('1/1/2017') + recno() -1)))-6) as [Period],

            If(Month(Date(Date#('1/1/2017') + recno() -1)) < 7,

                        Dual(''&Month(Date(Date#('1/1/2017') + recno() -1)), Month(Date(Date#('1/1/2017') + recno() -1))+6),

                        Dual(''&Month(Date(Date#('1/1/2017') + recno() -1)), Month(Date(Date#('1/1/2017') + recno() -1))-6)) As Month,

            num(If(Month(Date(Date#('1/1/2017') + recno() -1)) < 7,

                        Dual(''&Month(Date(Date#('1/1/2017') + recno() -1)), Month(Date(Date#('1/1/2017') + recno() -1))+6),

                        Dual(''&Month(Date(Date#('1/1/2017') + recno() -1)), Month(Date(Date#('1/1/2017') + recno() -1))-6))) As [Month No],

            Week(Date(Date#('1/1/2017') + recno() -1)) as Week,

            Day(Date(Date#('1/1/2017') + recno() -1)) as Day,

                Date(MonthEnd(Date(Date#('1/1/2017') + recno() -1)),'MMM-YY') as [Month Year],

                WeekDay(Date(Date#('1/1/2017') + recno() -1)) as WeekDay

            autogenerate Date#('1/1/2019') - Date#('1/1/2017') +1;

              • Re: master calender
                Jean-Baptiste COCHOIS

                Bonjour,

                Un Master Calendar tout simple qui fonctionne avec Qlik View ou Sense :

                 

                Par exemple avec le champ Date = OrderDate dans vos données et une période donnée :

                // Les dates effectives des ventes dans le tableau sont du 21 Janvier 2011 au 29 Septembre 2014

                // On étend le calendrier pour avoir une année complète du 01 Janvier 2011 au 31 Décembre 2014

                // Je n'utilise pas PEEK parce que je veux seulement une période définie

                 

                LET vMinDate = Num(Makedate(2015,1,1));

                LET vMaxDate = Num(Makedate(2017,12,31));

                 

                ChampDate:

                LOAD

                    date($(vMinDate)+IterNo()-1) as TempDate

                    AUTOGENERATE (1)

                    WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

                 

                Calendrier:

                LOAD

                    TempDate as OrderDate,

                    Year(TempDate) as Year,

                    'Q ' & Num(Ceil(Month(TempDate)/3),'(ROM)0') as Quarter,

                    Month(TempDate) as Month,

                    Dual(Year(TempDate)&'-Q'&Num(Ceil(Num(Month(TempDate))/3)),QuarterStart(TempDate)) as YearQuarter,

                    Dual(Year(TempDate)&'-'&Month(TempDate),Monthstart(TempDate)) as YearMonth,

                    Dual(Month(TempDate)&'-'&Year(TempDate),MonthStart(TempDate)) as MonthYear,

                    MonthEnd(TempDate) as MonthEnd,

                    MonthStart(TempDate) as MonthStart,

                    Week(TempDate) as Week,

                    Weekday(TempDate) as WeekDay,

                    Day(TempDate) as Day,

                 

                RESIDENT ChampDate;

                DROP TABLE ChampDate;

                 

                Ensuite on utilise les variables :

                // Pour info sur l'application

                Let vLastReload = Date(Now(), 'DD/MMM/YYYY hh:mm');

                Let vToday = Today(Now()); /*Date d'aujourd'hui*/

                 

                // CurrentDate

                Let vCYear  = '=Max(Year)';

                Let vCDate  = '=Max(OrderDate)';

                Let vCMonth  = '=Month(Max(OrderDate))';

                Let vCDay   = '=Day(Max(OrderDate))';

                 

                //PreviousDate

                Let vPYear  = '=(Max(Year)-1)';

                Let vPDate = '=Date(AddYears(Max(OrderDate),-1))';

                Let vPMonth  = '=Month(AddMonths(Max(OrderDate),-1))';

                Let vPDay = '=Day(Max(OrderDate)-1)';

                 

                // Year-2

                Let vLPYear = '=(Max(Year)-2)';

                 

                Exemple pour l'année courante

                Sum({$<Year={$(vCYear)}>}Sales)

                 

                Exemple d'un Rolling de mois M à mois M-12

                Rolling Sales sur 12 mois avec comme dimension YearMonth et comme expression

                sum({<YearMonth=, OrderDate= {">=$(=MonthStart(AddMonths(Max(OrderDate), -11)))<=$(=MonthEnd(AddMonths(Max(OrderDate), 0)))"} >}Montant)

                 

                Un lien utile Calendrier sur QlikView

                 

                J-B

              • Re: master calender
                Oussema Laaribi

                See this scripts :

                QuartersMap: 

                MAPPING LOAD  

                rowno() as Month, 

                'Q' & Ceil (rowno()/3) as Quarter 

                AUTOGENERATE (12); 

                 

                 

                Temp: 

                Load 

                               min("%Date Vente") as minDate, 

                               max("%Date Vente") as maxDate 

                Resident Vente; 

                 

                 

                Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

                Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

                 

                 

                DROP Table Temp; 

                  

                TempCalendar: 

                LOAD 

                               $(varMinDate) + Iterno()-1 As Num, 

                               Date($(varMinDate) + IterNo() - 1) as TempDate 

                               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                 

                MasterCalendar: 

                Load 

                               TempDate AS "%Date Vente", 

                               week(TempDate) As Week, 

                               Year(TempDate) As Year, 

                               Month(TempDate) As Month, 

                               Day(TempDate) As Day, 

                               YeartoDate(TempDate)*-1 as CurYTDFlag, 

                               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                               WeekDay(TempDate) as WeekDay 

                Resident TempCalendar 

                 

                 

                Order By TempDate ASC; 

                Drop Table TempCalendar;