Hi Everyone.
First is important to clarify that i am a beginner in Qlikview, for that reason understand sometimes the sintaxis is quite complicated, but i´m here to learn, so lets go ahead with my problem:
I have an Excel file that contains:
Database:
LOAD [PM Name],
Customer,
Region,
Country,
[Site ID],
[Order Submission Date],
[Order ID],
Status,
TOWER,
Category,
[OTC (USD)],
[MRC (USD)],
[BASELINE (date - TBD)],
PLANNED,
ACTUAL,
REMARKS,
[Item Type],
[Rvenu status],
[Revenue shared]
From this file i have 4 fields with dates (Order Submission Date, BASELINE (date-TBD), PLANNED, ACTUAL) the first thing that i need is to create a Fiscal calendar starting the year on April. With this new information i can create the list boxes to make the drill down from the information.
The second development that i need is to create a Canonical calendar in order to create bar charts or combo Charts where we can see the amount of money planned to receive Vs Money really received, using as dimension Year or Month.
For the Fiscal calendar i made it like this:
SET vFiscalYearStartMonth = 4;
LET vStartDate = Num(YearStart(Today(), -4));
LET vEndDate = Num(YearEnd(Today()));
FiscalCalendar:
LOAD
*,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd([BASELINE (date - TBD)]), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
LOAD
*,
Year([BASELINE (date - TBD)]) AS Year, // Standard Calendar Year
Month([BASELINE (date - TBD)]) AS Month, // Standard Calendar Month
Date(MonthEnd([BASELINE (date - TBD)]), 'MMM') AS MonthName, // Standard Calendar Month Name
Dual('Q' & Ceil(Month([BASELINE (date - TBD)])/3), Ceil(Month([BASELINE (date - TBD)])/3)) AS Quarter, // Standard Calendar Quarter
Mod(Month([BASELINE (date - TBD)]) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month
YearName([BASELINE (date - TBD)], 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS [BASELINE (date - TBD)],
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;
This Fiscal calendar Works ok, so the next step was to develop Canonical Calendar as follows:
DateBridge:
LOAD [Order ID], [BASELINE (date - TBD)] as CanonicalDate,'Baseline' as DateType;
LOAD [Order ID], [PLANNED] as CanonicalDate,'Planned' as DateType;
LOAD[Order ID],[ACTUAL] as CanonicalDate, 'Achieved' as Datetype;
//CODE FOR CREATING MASTER CALENDAR//
QuartersMap:
MAPPING LOAD
rowno() as MonthC,
'Q' & Ceil (rowno()/3) as QuarterC
AUTOGENERATE (12);
Temp:
Load
min( RangeMin( [BASELINE (date - TBD)], PLANNED, ACTUAL )) as minDate,
max( RangeMax( [BASELINE (date - TBD)],PLANNED, ACTUAL )) as maxDate;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
CanonicalCalendar:
Load
TempDate AS CanonicalDate,
week(TempDate) As CanonicalWeek,
Year(TempDate) As CanonicalYear,
Month(TempDate) As CanonicalMonth,
Day(TempDate) As CanonicalDay,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as CanonicalMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as CanonicalQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as CanonicalWeekYear,
WeekDay(TempDate) as CanonicalWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
When i try to reload the data appears many errors and any Canonical Data is created, i dont know what are my mystakes or if i am missin something in the process.
I really appreciate if someone can help me with my doubts.
Thanks
