Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
davyqliks
Creator III
Creator III

How to stop calendar starting at Dec 1899

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

2 Solutions

Accepted Solutions
martinpohl
Partner
Partner

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

View solution in original post

martinpohl
Partner
Partner

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

View solution in original post

5 Replies
martinpohl
Partner
Partner

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

View solution in original post

davyqliks
Creator III
Creator III
Author

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
Partner
Partner

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

View solution in original post

davyqliks
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

;