How to stop calendar starting at Dec 1899

[davyqliks]

After loading master calendar i see dates start at Dec 1899.

I am using a num function for converting dates and believe this is the reason. but how can i start my calendar at the date of the first order placed and not Dec 1899.

Thank you

[martinpohl · Accepted solution]

MinMax:

load 

date 

resident YourOrdertable

order by date

where date > 0;

let vMindate = peek('date',0,'MinMax');

let vMaxdate = peek('date',-1,'MinMax');

Now you can create a calendar from Min to Max

Regards

[davyqliks]

Thank you do much for the quick reply,

I currently have an Order by tab with the following:

Sales_final:
NoConcatenate

load
* ,
'Actual'as %_DataType
Resident SalesFinal
Order by %VoucherDate asc
;

Drop Table SalesFinal;

followed by a calendar tab with this.

LET v.Load.Date.Min = Num(Peek('%VoucherDate', 0, 'Sales_final')) ;
LET v.Load.Date.Max = Num(Peek('%VoucherDate', -1, 'Sales_final')) ;

// Create full date range
DateField:
LOAD
$(v.Load.Date.Min) + rowno() - 1 as Num,
date($(v.Load.Date.Min) + rowno() - 1) as TempDate
AUTOGENERATE
$(v.Load.Date.Max) - $(v.Load.Date.Min) + 1;

I cannot understand why i am still getting the older than data dates.

do you have any advice on how to amend  this?... Thanks again for your help in advance

Daniel

[martinpohl · Accepted solution]

I think there are some lines in your data with an empty voucher date.

so for that I suggested to load the date-field in a seperate table but only those where the value is greather than 0.

Regards

[davyqliks]

Thank you again Martin,

I have tried to add this line to my order by tab however i am getting a syntax error on the

where %VoucherDate > 0 

line.

Can you see from the below why this is? Much appreciated.

Sales_final:
NoConcatenate

load
* ,
'Actual'as %_DataType
Resident SalesFinal
Order by %VoucherDate asc
where %VoucherDate > 0
;

Drop Table SalesFinal;

[davyqliks]

Thanks again for this Martin,

I realised i had to have the where clause before the order by.

This has worked and removed the older than Data

load
* ,
'Actual'as %_DataType
Resident SalesFinal

where %VoucherDate > 0

Order by %VoucherDate asc

;