Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

how to create fiscal calender with existing data

how to create fiscal calender with existing data

KK
5 Replies
MVP & Luminary
MVP & Luminary

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

- Marcus

Partner
Partner

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

Creator III
Creator III

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

KK
Creator III
Creator III

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

KK
Partner
Partner

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