Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

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

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.

Highlighted
hariprasadqv
Contributor III

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

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

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

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

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

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

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

Thanks shiva..

Not applicable

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

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

buzzy996
Honored Contributor II

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

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

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

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

buzzy996
Honored Contributor II

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

okie,u can try with above master calender script

Not applicable

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

thank you shiva i am working on it.