Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

sahajaqlik
Contributor

master calender

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

4 Replies

Re: master calender

search on community there is multiple thread available for master calendar

try below

Creating A Master Calendar

modify it as per your requirement.

Regards,

Regards,
Prashant Sangle
jyothish8807
Honored Contributor II

Re: master calender

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;

Best Regards,
KC
Highlighted
adj29block
New Contributor III

Re: master calender

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

oussema_laribi
New Contributor

Re: master calender

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;

Community Browser