Master Calander Creation

[#1]

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 !

[MK_QSL]

T1:

LOAD [Product Sale],

     Bill_Date as billdate,

     Date(billdate) 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;

[lironbaram]

hi

you need to change your load of the table to

LOAD

     Date(Min(billdate)) AS MinDate,

Date(Max(billdate)) AS MaxDate

FROM

[excel data.xlsx]

(ooxml, embedded labels, table is Sheet1);

that way you'll get only one row in the table

the error you got is because you used  aggregation functions without a group by

[#4]

Thanks for response,

geting same Errore .when i calculate min date and max date i tried with both .

Errore as:

Temp:

Load Min(billdate) as MinDate, Max(billdate) as MaxDate Resident T1;

Not Found.

Please Respond me I am attaching my sample data and my app in new Discussion

[MK_QSL]

check below that the table name i have given is T1

T1:

LOAD [Product Sale],

     Bill_Date as billdate,

     Date(billdate) AS billdate

FROM

[excel data.xlsx]

(ooxml, embedded labels, table is Sheet1);

[MK_QSL]

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

[ecolomer]

See this: