Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have transaction date column in the excel sheet. I want to show the data in week wise,quarter wise and week ending date.Can anyone help me with this.
Use this link.
Hi,
There are lots of posts on here about calendars, just search for master calendar and would should find lots of help.
Mark
try this
//citesc fisierul cu sarbatorile legale
holydays_t:
LOAD * inline [holydays
1/1/2016
2/2/2016
3/3/2016
7/1/2016] ;
//concatenez toate sarbatorile legale
NoConcatenate
holydays:
load
concat(distinct CHR(39)&holydays&CHR(39),',') as concat_holydays
resident holydays_t;
drop table holydays_t;
//creez o variabila cu toate sarbatorile legale concatenate
let v_holydays=peek('concat_holydays',0,'holydays');
drop table holydays;
//exit script;
//creez o tabela de timeline incepand cu 1/1/2016 pana la sfarsitul lunii curente
TempCalendar:
LOAD
date('1/1/2016' + rowno() - 1,'DD-MM-YYYY') AS DATA
AUTOGENERATE 1 While '1/1/2016' + rowno() - 1< monthend(today())-1;
//la tabela de timeline adaus anul, luna si daca ziua respectiva este lucratoare sau nu (aici tinem cont si de sarbatorile legale)
NoConcatenate
pontaje_finale:
load
*,
NetWorkDays(DATA,DATA,$(v_holydays)) as days_work;
load
*,
month(DATA) as luna,
year(DATA) as an,
'S'&ceil(Month(date(DATA,'MMM-YY') )/6) as Semestru,
Year(date(DATA,'MMM-YY') )&'-S'&ceil(Month(date(DATA,'MMM-YY') )/6) as An_Semestru,
'Q'&ceil(Month(date(DATA,'MMM-YY') )/3) as Trimestru,
dual(Year(date(DATA,'MMM-YY'))&'-Q'&ceil(Month(date(DATA,'MMM-YY'))/3),num(QuarterStart(date(DATA,'MMM-YY')))) as An_Trim,
date(makedate(year(DATA),month(DATA),'01'),'MMM-YYYY') as AnLuna,
floor(monthstart(date(makedate(year(DATA),month(DATA),'01'),'MMM-YY'))) as AnLuna_today,
'W'&week(date(DATA,'MMM-YY')) as Saptamana,
dual (year(DATA) &'-w'& week(date(DATA,'MMM-YY')) , num(weekstart(date(DATA,'MMM-YY')))) as AnSapt
resident TempCalendar;
drop table TempCalendar;
Thanks,
But i am getting an error as below while reloading the script;
Error is as below:
Table not found
Calender:
Load
TempDate as [Start Date],
week(TempDate) as Week_Number,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter
Resident TempCalendar
Order By TempDate ASC
Pls help
Use below links
http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar
http://community.qlik.com/docs/DOC-3858
or Use following script
//***********CREATE VARIABLES***********
LET varMinDate = Num(Peek('Date',0,'TableName'));
LET varMaxDate = Num(Peek('Date',-1,'TableName'));
LET varToday = Num(Today());
//***********TEMPORARY CALENDAR***********
TempDateField:
LOAD
$(varMinDate) + RowNo() -1 as Num,
date($(varMinDate) + RowNo() -1) as TempDate
AutoGenerate
$(varMaxDate) - $(varMinDate) + 1;
//***********MASTER CALENDAR***********
MasterCalendar:
LOAD *,
TempDate as OrderDate,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
'Q' & Ceil(Month(TempDate)/3) as Quarter,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
Week(TempDate)&'-'&Year(TempDate) as WeekYear,
InYearToDate(TempDate,$(varToday),0) * -1 as CurYTDFlag,
InYearToDate(TempDate,$(varToday),-1) * -1 as LastYTDFlag
Resident TempDateField
Order By TempDate Asc;
DROP Table TempDateField;