how to create fiscal calender with existing data

[Karim_Khan]

how to create fiscal calender with existing data

[marcus_sommer]

This will be helpful: How to use - Master-Calendar and Date-Values

- Marcus

[prashantbaste]

Hi

Try below code -

Temp:

LOAD Min(TranDate) AS minDate,

     Max(TranDate) AS maxDate

Resident CREDITORSData Where TranDate < Today();

Let varMinDate = Num(Peek('minDate'));

Let varMaxDate = Num(Peek('maxDate'));

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

     Week(TempDate) As WeekYearly,

     Year(TempDate) As Year,

     Month(TempDate) As Month,

     Day(TempDate) As Day,

     'Q'&Ceil(Num(month(TempDate))/3) AS Quarter,

     'Half Year '&Ceil(Num(month(TempDate))/6) AS HalfYearly,

     'FortNighty '&Ceil (Day(TempDate)/14) AS FortNightly,

     'Week '&if(Ceil (Day(TempDate)/7)>3,4,Ceil (Day(TempDate)/7)) AS WeekMonthly,

     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) AS WeekYear,

     WeekDay(TempDate) as WeekDay,

     WeekEnd(TempDate) as CalendarWeekEnd,

     Text(Date($(varMaxDate), 'dd-MM-YYYY')) AS DayName

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Make necessary changes as per your requirements.

Hope this will be helpful for you.

--

Regards,

Prashant P Baste

[Karim_Khan]

Cisco:

LOAD [End Customer Global Ultimate Name],

     Country,

     [End Customer HQ Top],

     [Sales Level 1],

     [Sales Level 2],

     [Sales Level 3],

     [Sales Level 4],

     [Sales Level 5],

     [Partner Name],

     [Fiscal Period ID],

     Year(Date#([Fiscal Period ID],'YYYYMM')) as Year,

    Month(Date#([Fiscal Period ID],'YYYYMM')) as Month,

     Date(Num(Date#([Fiscal Period ID],'YYYYMM')),'DD-MM-YYYY') as Date,

     FMV,

     [Net Bookings],

     Group

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Copy of June raw.xlsx]

(ooxml, embedded labels, table is Sheet1);

SET vFiscalYearStartMonth = 8;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

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

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

LOAD

*,

Year(Date) AS CiscoYear, // Standard Calendar Year

Month(Date) AS CiscoMonth, // Standard Calendar Month

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

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

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

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

LOAD

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

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

AutoGenerate vEndDate - vStartDate + 1;

am getting the error

[Karim_Khan]

Cisco:

LOAD [End Customer Global Ultimate Name],

     Country,

     [End Customer HQ Top],

     [Sales Level 1],

     [Sales Level 2],

     [Sales Level 3],

     [Sales Level 4],

     [Sales Level 5],

     [Partner Name],

     [Fiscal Period ID],

     Year(Date#([Fiscal Period ID],'YYYYMM')) as Year,

    Month(Date#([Fiscal Period ID],'YYYYMM')) as Month,

     Date(Num(Date#([Fiscal Period ID],'YYYYMM')),'DD-MM-YYYY') as Date,

     FMV,

     [Net Bookings],

     Group

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Copy of June raw.xlsx]

(ooxml, embedded labels, table is Sheet1);

SET vFiscalYearStartMonth = 8;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

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

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

LOAD

*,

Year(Date) AS CiscoYear, // Standard Calendar Year

Month(Date) AS CiscoMonth, // Standard Calendar Month

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

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

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

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

LOAD

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

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

AutoGenerate vEndDate - vStartDate + 1;

am unable to connect my QVD script with this Fiscal Script

[prashantbaste]

Hi

can you share the error message?

By the way you can try -

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

Check & confirm.

--

Regards,

Prashant P Baste