topic Re: Calendar in QlikView

Calendar

smilingjohn — Wed, 14 Oct 2015 15:03:16 GMT

Hi All,

Iam trying to generate the master calendar , but its not working , Can please spome one look into this ?

PLease find the attachment !.

I referd all the community post , I have a column called Date in my excel sheet , I tried all byut its not working .

Can some one help me out please

Re: Calendar

uroboros — Wed, 14 Oct 2015 15:07:10 GMT

Hi, to do it, let see this post, it work,
Master Calendar Data

Re: Calendar

sunny_talwar — Wed, 14 Oct 2015 15:13:24 GMT

See if the attached file helps. Although I believe you need to implement a master calendar as well as Master Time Table: The Master Time Table

Re: Calendar

smilingjohn — Wed, 14 Oct 2015 15:13:32 GMT

HI Padila

I have my date field coming from the excel sheet , How do i do it ?

Re: Calendar

miguelbraga — Wed, 14 Oct 2015 15:14:01 GMT

Hi John, take a look at this document from Henric:

The Master Calendar

Or take a look at the regular Master Calendar template and try to acomodate to your solution:

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(OrderDate) as minDate,

max(OrderDate) as maxDate

Resident Orders;

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 OrderDate,

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;

Re: Calendar

smilingjohn — Wed, 14 Oct 2015 15:21:01 GMT

Thanks a lot sunny , But how do i add week and YTD fiscal year ,.

If you see there seems to be gap in between , the fiscal date is showing -- for actual date w, can you please explain me why is so ?

Please find the attachment .

Re: Calendar

uroboros — Wed, 14 Oct 2015 15:21:59 GMT

It work's equals, you have a date on your field
descompose it in years, months, or like you wanted; and create a new table with this data; i Will work
Something like this:

facts:

load

field0 as id (like a PK)

field1 as information (as an example)

field2 as date

from [exceldocument.xlsx]; (it is an example)

calendar:

id,

year(date) as year,

month(date) as month

resident facts;

drop fields date;

And voaila! you have a master calendar.

Re: Calendar

smilingjohn — Wed, 14 Oct 2015 15:34:51 GMT

HI Padila,

The attachment sent by Sunny talwar is working , But when i copy the master calendar data in my script and try to run its not working . I dontknow what trick is there behind it . I just wanted to add this YTD fiscal month and year.

Re: Calendar

Anonymous — Fri, 29 Apr 2016 06:56:44 GMT

Hi Sunny,

I am getting the below when ever I ran this query. Can you help me here?

Script line error:

LOAD Date( + RangeSum(Peek('RowNum'), 1) - 1) AS [Order Entry Date],

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1

Query I ran is :

SET vFiscalYearStartMonth = 4;

DateRange:
LOAD Min([Order Entry Date]) AS MinDate,
Max([Order Entry Date]) AS MaxDate
Resident Prediction;

LET vStartDate = Peek('MinDate');
LET vEndDate = Peek('MaxDate');

DROP TABLE DateRange;

FiscalCalendar:
LOAD *,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd([Order Entry Date]), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
LOAD *,
Year([Order Entry Date]) AS Year, // Standard Calendar Year
Month([Order Entry Date]) AS Month, // Standard Calendar Month
Date(MonthEnd([Order Entry Date]), 'MMM') AS MonthName, // Standard Calendar Month Name
Dual('Q' & Ceil(Month([Order Entry Date])/3), Ceil(Month([Order Entry Date])/3)) AS Quarter, // Standard Calendar Quarter
Mod(Month([Order Entry Date]) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month
YearName([Order Entry Date], 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year
LOAD Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS [Order Entry Date],
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;

Re: Calendar

sunny_talwar — Fri, 29 Apr 2016 06:59:55 GMT

Looking at the error, it seems that your [Order Entry Date] might not be read as date. Check these blogs

Get the Dates Right

Why don’t my dates work?‌

and make sure that your dates are read properly by QlikView