topic Master Calander Creation in QlikView

Master Calander Creation

Sat, 24 Oct 2015 16:21:27 GMT

HI All,

i am creating calander ,but some errore in script for creating min and max data could u please help to resolve this errore !

do you have any app with sample data upload me it will use full me please

Re: Master Calander Creation

MK_QSL — Sat, 24 Oct 2015 16:32:56 GMT

T1:

LOAD

[Product Sale],

Date(Bill_Date) AS billdate

FROM

[excel data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

Load

Min(billdate) as MinDate,

Max(billdate) as MaxDate

Resident T1;

SET vFiscalYearStartMonth = 4;

LET vStartDate = num(Peek('MinDate',0,'Temp'));

LET vEndDate = num(Peek('MaxDate',0,'Temp'));

DROP TABLE Temp;

FiscalCalendar:

LOAD

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(billdate), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

Year(billdate) AS Year, // Standard Calendar Year

Month(billdate) AS Month, // Standard Calendar Month

Date(MonthEnd(billdate), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(billdate)/3), Ceil(Month(billdate)/3)) AS Quarter, // Standard Calendar Quarter

Mod(Month(billdate) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month

YearName(billdate, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS billdate,

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

Re: Master Calander Creation

ecolomer — Sat, 24 Oct 2015 16:58:11 GMT

See this file

Re: Master Calander Creation

Sat, 24 Oct 2015 17:01:35 GMT

Thanku Manish,

now it is reloading no errore ,good but only two fields i am able to see like billdate and Product sale where are the rest like FYYear,Fyquarter,FyMonth

Re: Master Calander Creation

MK_QSL — Sat, 24 Oct 2015 17:17:33 GMT

Check this