topic master calender in QlikView

master calender

qlikapple — Wed, 25 Nov 2020 16:16:04 GMT

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

PrashantSangle — Mon, 19 Mar 2018 06:45:14 GMT

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

jyothish8807 — Mon, 19 Mar 2018 07:05:37 GMT

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

adj29block — Fri, 23 Mar 2018 09:13:02 GMT

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_laribi — Fri, 23 Mar 2018 09:23:08 GMT

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;