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: 
Not applicable

hi friends i want to create a calendar dased on date field.

HI friends  i want to create a calendar based on date field but the problem is that date field contains only yyyy-mm.

25 Replies
jpenuliar
Partner - Specialist III
Partner - Specialist III

You can use this new field to pull MinDate and MaxDate for the Calendar.

MaxDate will be on the first of that Month, you will want to add 1 month to the max date pulled.

hariprasadqv
Creator III
Creator III

Hi Ankith, Try this

LOAD Sales,

    Purchases,

    [Gross Profit],

    Overheads,

    [Operating Profit],

    [Other Expense],

    [Other Income],

    [Other Expense/Income],

    [Net Income],

    Month as date,

    YEAR(Month) as Year,

    Month(Month) as Months,

    Num(Month(Month)) as MonthNumber,

    'Q' & ceil(month(Month) / 3) as Quarter,

'Q' & Ceil(Month(Month)/3) & '-' & Year(Month) as QuarterYear,

FROM

(biff, embedded labels);



Along with the above the fallowing links will help you.


https://community.qlik.com/message/746637?et=watches.email.thread#746637


Re: Creating A Master Calendar


Creating A Master Calendar

buzzy996
Master II
Master II

from the script shared earlier u have date field that is.NewMonth (u can rename it),by using &following the below script u can create master calendar.

Master Calendar:

//***********************************************************************************

//This page builds a master calendar table with both fiscal and calendar based fields

//***********************************************************************************

//This is the number of months that the fiscal periods are offset against the calendar

//periods. A value of 6 results in a July to June fiscal year.

SET vFiscalOffset = 6;

//----------------------------- MinMax Table -----------------------------------------

//Get start and end Date from Facts table, so we know what range of dates to build the

//calendar for. You need to work out what is the date field in your data model that you

//will be linking your calendar to. In this case we have used OrderDate.

//------------------------------------------------------------------------------------

//Get the max and min values for OrderDate and round up/down to start/end of month dates

//Use the AddMonths function to add X months to the MaxDate so we can forecast out

MinMax:

LOAD

  monthstart(Min(OrderDate)) as MinDate,

  monthend(AddMonths(Max(OrderDate), 9)) as MaxDate,

  monthend(Max(OrderDate)) as LastDate

RESIDENT OrderHeader;

//Assign the max and min values to variables so we can use them in our script

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vLastDate = Num(Peek('LastDate', 0, 'MinMax'));

//Set vToday equal to the maximum date as the data set is not updating. However we would

//normally set the variable to todays date using  LET vToday = num(Today());

LET vToday = $(vLastDate);

//Assign values directly if have no fact table to get the min & max from

//----------------------------------------------------------------------

//LET vMinDate = Num(makedate(2011));

//LET vMaxDate = Num(makedate(2013, 12, 31));

//LET vToday = $(vMaxDate);

//------------------------ Temporary Calendar -----------------------------------------

//We now generate a table containing all the dates between our MinDate and MaxDate values

//-------------------------------------------------------------------------------------

TempCal:

LOAD

  date($(vMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

  $(vMaxDate) - $(vMinDate) + 1;

//------------------------ Master Calendar ---------------------------------------------

//Using the list of dates created in TempCal we can now create the master calendar

// We name our unique date based key OrderDate so that it links back to our data model

//---------------------------------------------------------------------------------------

LET vThisMthID = (year($(vToday))*12) + month($(vToday));

MasterCalendar:

LOAD

  TempDate AS OrderDate,

  Weekday(TempDate) AS WeekDay,

  Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

  //calendar year fields

  Week(TempDate) AS CalWeek,

  Month(TempDate) AS CalMonth,

  Year(TempDate) AS CalYear,

  Day(TempDate) AS CalDay,

  Week(TempDate)&'-'&Year(TempDate) AS CalWeekYear,

  //fiscal year fields

  num(Month(addmonths(TempDate, $(vFiscalOffset)))) AS FiscalPeriod,

    Dual(Month(TempDate), num(month(monthstart(TempDate, $(vFiscalOffset))))) as FiscalMth,

  num(year(addmonths(TempDate, $(vFiscalOffset)))) AS FiscalYear,

    'Q' & ceil(month(monthstart(TempDate, $(vFiscalOffset))) / 3) AS FiscalQtr,

    num(year(addmonths(TempDate, $(vFiscalOffset)))) + ' ' + 'Q' & ceil(month(monthstart(TempDate, $(vFiscalOffset))) / 3) AS FiscalYearQtr,

  //The following flags are useful for Set Analysis formulas, to get specific date based sub-sets of data

  //Assume current month is the last COMPLETE month (which is actually prior mth)

  if(monthstart(TempDate)=monthstart(AddMonths($(vToday), -1)), 1, 0) AS CurMTDFlag,

  if(monthstart(TempDate)=monthstart(AddMonths($(vToday), -2)), 1, 0) AS PriorMTDFlag,

  //Assume current week is the last COMPLETE week (which is actually prior wk)

  if(WeekStart(TempDate)=WeekStart($(vToday)-7), 1, 0) AS CurWeekFlag,

  if(WeekStart(TempDate)=WeekStart($(vToday)-14), 1, 0) AS PriorWeekFlag,

  if(TempDate>=monthstart(AddMonths($(vToday), -12)) AND TempDate<monthstart($(vToday)), 1, 0) as Rolling12MthFlag,

  if(TempDate>=monthstart(AddMonths($(vToday), -24)) AND TempDate<monthstart(AddMonths($(vToday), -12)), 1, 0) as RollingPrior12MthFlag,

  if(TempDate >= monthstart($(vLastDate)), 1, 0) AS ForecastFlag,

  (year(TempDate)*12) + month(TempDate) - $(vThisMthID) as CurYearMthIndex

RESIDENT TempCal

ORDER BY TempDate ASC;

//------------------------------------------------------------------------

//Drop the temporary tables that we created as we dont need them any more

//------------------------------------------------------------------------

DROP TABLE TempCal;

DROP TABLE MinMax;

Not applicable
Author

HI jonathan thanks for ur reply,

i am using this script to generate calendar :

tmp:

LOAD

min(MonthDate) as MinDate,

  max(MonthDate) as MaxDate

  Resident pnl;

LET Start = floor(YearStart(peek('MinDate')));

LET End = floor(YearEnd(peek('MaxDate')));

Drop Table tmp;

LET NumOfDays = End - Start + 1;

Date_src:

LOAD

$(Start) + Rowno() -1 as DateId

AUTOGENERATE $(NumOfDays);

calendar:

load

DateId,

Month(DateId) as Month,

Date(DateId) as Date1,

Year(DateId) as Yearrr,

Month(DateId) as Monthss,

'Q' & ceil(Month(DateId)/3) as Quarter

RESIDENT Date_src;

Drop Tables Date_src;

Not applicable
Author

Thanks shiva..

Not applicable
Author

Somehow correct jonathan , but i have test it on live data ..

buzzy996
Master II
Master II

i think if ur looking to generate calendar dimension,the .qvw shared earlier generated that calendar also.

u can add rest of the fields whichever u required in can add the corresponding script in that date dimension,if i'm not wrong.

Not applicable
Author

no that was for calendar object shiva i have create master calendar at script level.

buzzy996
Master II
Master II

okie,u can try with above master calender script

Not applicable
Author

thank you shiva i am working on it.