Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
how to create fiscal calender with existing data
This will be helpful: How to use - Master-Calendar and Date-Values
- Marcus
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
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
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
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;
am getting the error
am unable to connect my QVD script with this Fiscal Script
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.
