Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
search on community there is multiple thread available for master calendar
try below
modify it as per your requirement.
Regards,
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;
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
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;