Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI friends i want to create a calendar based on date field but the problem is that date field contains only yyyy-mm.
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.
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
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;
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;
Thanks shiva..
Somehow correct jonathan , but i have test it on live data ..
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.
no that was for calendar object shiva i have create master calendar at script level.
okie,u can try with above master calender script
thank you shiva i am working on it.