Return a month over mm/dd/yyyy not working

[#1]

Hello,

I need some help. I have a list of Travel and Expense reports (Air tickets, Hotel, Train, etc.). I'm having a fundamental problem. One of the fields is [Travel Start Date] that has a Time format MM/DD/YYYY. Not all of the lines of the report has that information, and I will have more than one line or ticket the same starting date [Travel Start Date].

I have a few fundamental problem. I can't create a single Filter Pane using that field and extract only the Year of that list of values (2015, 2016, 2017).

Thanks a lot in advance.

[Anil_Babu_Samineni]

Would you provide sample data and then show your Expected result to see

[vinieme12]

If you want to do it load

Load *

Year(TravelDate) as TravelYear

From YourTable;

If you want to do directly in listbox then

Aggr(only({<TravelDate>} Year(TravelDate) ) , TravelDate)

[vinieme12]

you might also want to read about Master calendars

Use of Master Calendar

https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

[rahulpawarb]

Hello Ignacio,

I believe that problem is records without [Travel Start Date] will be excluded from the report if you select a year in the filter. It this is the case then use below expression in filter object.

=Alt(Year(Date#([Travel Start Date],'MM/DD/YYYY')),Year(Today()))

NOTE: I selected [Travel Start Date] field from inline table that's why I added Date# function to interpret it as a date. If my records don't have any [Travel Start Date] value then I am populating Current Year value(You can define any value of your choice.

Thank you!

Rahul

[#6]

Thanks a lot for your help.