Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikapple
Creator
Creator

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
PrashantSangle

search on community there is multiple thread available for master calendar

try below

Creating A Master Calendar

modify it as per your requirement.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
jyothish8807
Master II
Master II

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
adj29block
Contributor III
Contributor III

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
Contributor II
Contributor II

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;