i have a ticket date from a xml file, i will split the date in year,week,month,day
i am using the mastercalander script;
he shows in the frontent example;
year 56989 = 2012
date 20120602
script master clender
Let varMinDate = NUM(PEEK('OrderDate' , 0, 'Orders'));
Let varMaxDate = NUM(PEEK('OrderDate' , -1, 'Orders'));
Let varToday = NUM(Today());
Let vJulianStartDate = Date(1);
// Important note, always check variables before you continue!
TempCalendar: Load $(varMinDate) + RowNo() -1 AS Num, Date($(varMinDate) + RowNo() -1) AS TempDate AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;
MasterCalendar: Load TempDate as OrderDate, Week(TempDate) as Week, Year(TempDate) as Year, Month(TempDate) as Month, Day(TempDate) as Day, WeekDay(TempDate) as WeekDay, 'Q' & CEIL (Month(TempDate)/3) AS Quarter, Date(MonthStart (TempDate), 'MMM-YYYY') as MonthYear, Week (TempDate) & '-' & Year(TempDate) as WeekYear, InYearToDate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag, InYearToDate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag RESIDENT TempCalendar Order by TempDate ASC;
If this is your standard date format, define it as such in the script, changing the line at the top of your script:
SET DateFormat='DDMMYYYY';
You'll still need to use the interpretation function date#() to read in your data. To format your date according your standard format, just use an additional date() function (note the subtile difference between date#() and date(), check the HELP for more details):
Yes, as I assumed, the key to your issue is in your Orders table load.
You need to tell QV to interprete 20120602 as Date, not as number (or as the numeric representation of a number, which will then result in a date in year 56988 (20,120,602 days since 30 Dec 1899) ).
When loading in your data, either set your standard date format to 'YYYYMMDD', or explicitely tell QV how to interpret your data using date#() function:
Orders:
LOAD
Date#(Date,'YYYYMMDD') as OrderDate,
Time,
WhoNr,
WhoName,
PcNr,
EcoNr,
EcoName,
PriceTag,
TaPriceTag,
%Key_Ticket_369F651F33B57014, // Key to parent table: Tickets/Ticket
%Key_Order_2A9BFA2C52FE6F55 // Key for this table: Tickets/Ticket/Order
FROM FX20120602.xml (XmlSimple, Table is [Tickets/Ticket/Order]);
If this is your standard date format, define it as such in the script, changing the line at the top of your script:
SET DateFormat='DDMMYYYY';
You'll still need to use the interpretation function date#() to read in your data. To format your date according your standard format, just use an additional date() function (note the subtile difference between date#() and date(), check the HELP for more details):
