My script year(today()) - year([Posting Date]) + 1 as Year_n, not working

[paulyeo11]

Hi All

I have below date field :-

@50:60T as [date],

I can get the Year_n using below load script :-

year(today()) - year(@50:60T) + 1 as Year_n,

I need to load one more sales order table , which come in excel. having below date filed :-

[Posting Date] as [date],

I use the above example for getting Year_n :-

year(today()) - year([Posting Date]) + 1 as Year_n,

But above load script cannot work. Can some one advise me why ?

Paul Yeo

[Vegar · Accepted solution]

My best guess would be that the field [Posting Date] is not recognized as a date.

How does your qlik system date format look like and how is the date format in excel?

What happens if you do this?

Dayname([Posting Date]) as Date

[paulyeo11]

Hi Sir

I try below :-

year(today()) - year(Dayname([Posting Date])) + 1 as Year_n,

Not error , but still not working , when i use below expression , it return 0 :-

Sum(if(Year_n='1',sales/1))/1000

Paul 

[paulyeo11]

Hi Sir,

I using master calendar date and now it work fine. Thank you for give me the hints.