Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Return a month over mm/dd/yyyy not working

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.

5 Replies
Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

you might also want to read about Master calendars

Use of Master Calendar

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
rahulpawarb
Specialist III
Specialist III

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

Not applicable
Author

Thanks a lot for your help.